SQL Server—收集信息

select a.name 'Job名称',
case when (c.freq_type=4 and c.freq_subday_type=4) then ('每'+convert(varchar,c.freq_subday_interval)+'分钟')
     when (c.freq_type=4 and c.freq_subday_type=8) then ('每'+convert(varchar,c.freq_subday_interval)+'小时')
     when (c.freq_type=4 and c.freq_subday_type=1) then ('每天一次:' + right('000000'+convert(varchar,active_start_time),6) )
     when (c.freq_type=8 and c.freq_interval=1 and c.freq_subday_type=1) then ('每星期天:' + right('000000'+convert(varchar,active_start_time),6) )
     when (c.freq_type=8 and c.freq_interval=2 and c.freq_subday_type=1) then ('每星期一:' + right('000000'+convert(varchar,active_start_time),6) )
     when (c.freq_type=8 and c.freq_interval=4 and c.freq_subday_type=1) then ('每星期二:' + right('000000'+convert(varchar,active_start_time),6) )
     when (c.freq_type=8 and c.freq_interval=8 and c.freq_subday_type=1) then ('每星期三:' + right('000000'+convert(varchar,active_start_time),6) )
     when (c.freq_type=8 and c.freq_interval=16 and c.freq_subday_type=1) then ('每星期四:' + right('000000'+convert(varchar,active_start_time),6) )
     when (c.freq_type=8 and c.freq_interval=32 and c.freq_subday_type=1) then ('每星期五:' + right('000000'+convert(varchar,active_start_time),6) )
     when (c.freq_type=8 and c.freq_interval=64 and c.freq_subday_type=1) then ('每星期六:' + right('000000'+convert(varchar,active_start_time),6) )
     when (c.freq_type=8 and c.freq_interval=1 and c.freq_subday_type=8) then ('每星期天:每' +convert(varchar,c.freq_subday_interval)+'小时')
else '未知' end '频率',
case when c.active_end_date=99991231 then '永久' else convert(varchar,c.active_end_date) end '期限'
, a.[description],b.*,c.*
  from msdb.dbo.sysjobs a with(nolock) inner join msdb.dbo.sysjobschedules b with(nolock)
    on a.job_id = b.job_id
 inner join msdb.dbo.sysschedules c with(nolock) on b.schedule_id=c.schedule_id
 where a.[enabled]=1 and c.[enabled]=1
 order by 1;

作者“小旭的技术博客”