作业信息获取

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;