[SQL Server]收集Agent job信息

逃离我推掉我的手 2022-06-18 04:49 181阅读 0赞

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;