USE MSDB
GO

SET NOCOUNT ON

create table #temp
([Job Name] varchar(128),
[Job Enabled] varchar(3),
[Plan Name] varchar(128),
[Job Type] varchar(50),
[Databases] varchar(1000),
[Schedule Name] varchar(128),
[Schedule Enabled] varchar(3),
[Schedule] varchar(20),
[Interval] varchar(200),
[Start Date] char(10),
[End Date] char(10)
)

declare @job_name varchar(128), @JobEnabled varchar(3),
 @plan_name varchar(128), @ScheduleName varchar(128), @ScheduleEnabled varchar(3),
@job_type varchar(50), @plan_id uniqueidentifier,
 @dbString varchar(1000), @freq_interval int, @frequency varchar(20),
@Interval varchar(200), @freq_type int, @freq_recurrence_factor int,
@freq_relative_interval int, @freq_subday_type int, @freq_subday_interval int,
@active_start_time int, @active_end_time int,
@xActive_start_time varchar(8), @xActive_end_time varchar(8),
@startdate char(10), @enddate char(10)

declare maintCursor cursor for
select sj.name as job_Name,
  case when sj.enabled = 1 then 'Yes'
  when sj.enabled = 0 then 'No'
  end
as JobEnabled,
sd.plan_id, sm.plan_name, sch.name, 
  case when sch.enabled = 1 then 'Yes'
  when sch.enabled = 0 then 'No'
  end
as ScheduleEnabled,
sch.freq_interval,
  case when js.command like '%-BkUpDB%' then 'Database backup job'
  when js.command like '%-BkUpLog%' then 'Transaction log backup job'
  when js.command like '%-CkDB%' then 'Integrity checks job'
  when js.command like '%-RebldIdx%' then 'Optimizations job'
  end
as [Job_Type],
  case when sch.freq_type = 16 or sch.freq_type = 32 then 'Monthly job'
  when sch.freq_type = 8 then 'Weekly Job'
  when sch.freq_type = 4 then 'Daily Job'
  end
as [Schedule],
sch.freq_type, sch.freq_recurrence_factor, sch.freq_relative_interval,
sch.freq_subday_type, sch.freq_subday_interval,
sch.active_start_time, sch.active_end_time,
sch.active_start_date, sch.active_end_date
from sysjobs sj inner join sysdbmaintplan_jobs sd
  on sj.job_id = sd.job_id inner join sysdbmaintplans sm
    on sd.plan_id = sm.plan_id inner join sysjobsteps js
      on sj.job_id = js.job_id inner join sysjobschedules sch
        on sch.job_id = sj.job_id
order by sj.name


open maintCursor

fetch next from maintCursor into @job_Name, @JobEnabled, @plan_id, @plan_name,
@ScheduleName, @ScheduleEnabled, @freq_interval, @job_type, @frequency,
@freq_type, @freq_recurrence_factor, @freq_relative_interval, @freq_subday_type,
@freq_subday_interval, @active_start_time, @active_end_time, @StartDate, @EndDate

while @@fetch_status = 0

begin

select @xActive_start_time = replicate('0', 6 - len(@active_start_time)) + cast(@active_start_time as varchar)
select @xActive_end_time = replicate('0', 6 - len(@active_end_time)) + cast(@active_end_time as varchar)
select @xActive_start_time = substring(@xActive_start_time, 1, 2) + ':' + substring(@xActive_start_time, len(@xActive_start_time) - 3, 2) + ':' + right (@xActive_start_time, 2)
select @xActive_end_time = substring(@xActive_end_time, 1, 2) + ':' + substring(@xActive_end_time, len(@xActive_end_time) - 3, 2) + ':' + right (@xActive_end_time, 2)

select @dbString = ''
select @Interval = ''

select @dbString = @dbString + database_name + ', ' from sysdbmaintplan_databases
where plan_id = @plan_id

if @freq_type = 4 
  begin
  select @Interval = @Interval + 'Every ' + cast(@freq_interval as varchar) + ' day(s).'
  if @freq_subday_type = 1 
    begin
    select @Interval = @Interval + ' Once at ' + @xActive_start_time + '.'
    end
  else
    begin
    select @Interval = @Interval + ' Every ' + cast(@freq_subday_interval as varchar) +
      case cast(@freq_subday_type as varchar)
      when '4' then ' minute(s)'
      when '8' then ' hour(s)'
    end
    select @Interval = @Interval + ', starting at ' + @xActive_start_time + ' ending at ' + @xActive_end_time + '.'
   end
  end

if @freq_type = 8 
  begin
  select @Interval = @Interval + 'Every ' + cast(@freq_recurrence_factor as varchar) + ' week(s) on'
    if @freq_interval & 1 = 1 select @Interval = @Interval + ' SUN'
    if @freq_interval & 2 = 2 select @Interval = @Interval + ' MON'
    if @freq_interval & 4 = 4 select @Interval = @Interval + ' TUE'
    if @freq_interval & 8 = 8 select @Interval = @Interval + ' WED'
    if @freq_interval & 16 = 16 select @Interval = @Interval + ' THU'
    if @freq_interval & 32 = 32 select @Interval = @Interval + ' FRI'
    if @freq_interval & 64 = 64 select @Interval = @Interval + ' SAT'
  select @Interval = @Interval + '.'
  if @freq_subday_type = 1 
    begin
    select @Interval = @Interval + ' Once at ' + @xActive_start_time + '.'
    end
  else
    begin
    select @Interval = @Interval + ' Every ' + cast(@freq_subday_interval as varchar) +
      case cast(@freq_subday_type as varchar)
      when '4' then ' minute(s)'
      when '8' then ' hour(s)'
      end
    select @Interval = @Interval + ', starting at ' + @xActive_start_time + ' ending at ' + @xActive_end_time + '.'
    end
  end

if @freq_type = 16
  begin
  select @Interval = 'Day ' + cast(@freq_interval as varchar) + ', every ' + cast(@freq_recurrence_factor as varchar) + ' month(s).'
    if @freq_subday_type = 1 
      begin
      select @Interval = @Interval + ' Once at ' + @xActive_start_time + '.'
      end
    else
      begin
      select @Interval = @Interval + ' Every ' + cast(@freq_subday_interval as varchar) +
        case cast(@freq_subday_type as varchar)
        when '4' then ' minute(s)'
        when '8' then ' hour(s)'
        end
      select @Interval = @Interval + ', starting at ' + @xActive_start_time + ' ending at ' + @xActive_end_time + '.'
    end
  end 

if @freq_type = 32
  begin
  select @Interval = 'The ' +
    case cast(@freq_relative_interval as varchar)
    when '1' then 'first '
    when '2' then 'second '
    when '4' then 'third '
    when '8' then 'fourth '
    when '16' then 'last '
    end
  +
    case cast(@freq_interval as varchar)
    when '1' then 'Sunday' 
    when '2' then 'Monday'
    when '3' then 'Tuesday'
    when '4' then 'Wednesday'
    when '5' then 'Thursday'
    when '6' then 'Friday'
    when '7' then 'Saturday'
    when '8' then 'day'
    when '9' then 'weekday'
    when '10' then 'weekend day'
    end
  +
  ', every ' + cast(@freq_recurrence_factor as varchar) + ' month(s).'
    if @freq_subday_type = 1 
      begin
      select @Interval = @Interval + ' Once at ' + @xActive_start_time + '.'
      end
    else
      begin
      select @Interval = @Interval + ' Every ' + cast(@freq_subday_interval as varchar) +
        case cast(@freq_subday_type as varchar)
        when '4' then ' minute(s)'
        when '8' then ' hour(s)'
        end
      select @Interval = @Interval + ', starting at ' + @xActive_start_time + ' ending at ' + @xActive_end_time + '.'
    end
  end 

Select @StartDate = substring(@StartDate, 5, 2) + '/' + substring(@StartDate, 7,2) + '/' + substring(@StartDate, 1, 4)
Select @EndDate = substring(@EndDate, 5, 2) + '/' + substring(@EndDate, 7,2) + '/' + substring(@EndDate, 1, 4)

  if right(@EndDate, 4) = '9999' 
  Select @EndDate = 'None'


insert into #temp
([Job Name], [Job Enabled], [Plan Name], [Job Type], [Databases], [Interval], [Schedule Name], [Schedule Enabled], [schedule], [Start Date], [End Date])
values
(@job_name, @JobEnabled, @plan_name, @job_type, @dbString, @Interval, @ScheduleName, @ScheduleEnabled, @frequency, @StartDate, @EndDate)

fetch next from maintCursor into @job_Name, @JobEnabled, @plan_id, @plan_name,
@ScheduleName, @ScheduleEnabled, @freq_interval, @job_type, @frequency,
@freq_type, @freq_recurrence_factor, @freq_relative_interval, @freq_subday_type,
@freq_subday_interval, @active_start_time, @active_end_time, @StartDate, @EndDate
end

close maintCursor
deallocate maintCursor

select [Plan Name], [Job Name], [Job Enabled],  [Job Type], left(Databases, len(databases)-1) as Databases,
[Schedule Name], [Schedule Enabled], Schedule as [Schedule Type], Interval, [Start Date], [End Date]
from #temp
order by [plan name], [Job Type], [Schedule Name]
drop table #temp