–需要增加一个二进制转换函数
USE msdb;
if not exists (select top 1 * from msdb.dbo.sysobjects where [name]=‘f_int2bin’ and [xtype]=‘FN’)
begin
declare @sqlstr varchar(1000)
set @sqlstr=’
create function dbo.[f_int2bin](@i int)
returns varchar(31)
as
begin
declare @s varchar(31);
set @s=’’’’
while (@i>0)
select @s=cast(@i%2 as varchar)+@s, @i=@i/2
return(@s)
end;’
exec(@sqlstr)
end
–查看作业
–基础数据
select
a.job_id,
a.name,
c.schedule_id,
c.freq_type,
c.freq_interval,
c.freq_subday_type,
case when substring(reverse(msdb.dbo.f_int2bin(c.freq_interval)),2,1)=‘1’ then ‘，星期一’ else ‘’ end +
case when substring(reverse(msdb.dbo.f_int2bin(c.freq_interval)),3,1)=‘1’ then ‘，星期二’ else ‘’ end +
case when substring(reverse(msdb.dbo.f_int2bin(c.freq_interval)),4,1)=‘1’ then ‘，星期三’ else ‘’ end +
case when substring(reverse(msdb.dbo.f_int2bin(c.freq_interval)),5,1)=‘1’ then ‘，星期四’ else ‘’ end +
case when substring(reverse(msdb.dbo.f_int2bin(c.freq_interval)),6,1)=‘1’ then ‘，星期五’ else ‘’ end +
case when substring(reverse(msdb.dbo.f_int2bin(c.freq_interval)),7,1)=‘1’ then ‘，星期六’ else ‘’ end +
case when substring(reverse(msdb.dbo.f_int2bin(c.freq_interval)),1,1)=‘1’ then ‘，星期日’ else ‘’ end as freq_subday_type1,
substring(right(‘000000’+convert(varchar,active_start_time),6),1,2)+’:’ + substring(right(‘000000’+convert(varchar,active_start_time),6),3,2) +’:’ + + substring(right(‘000000’+convert(varchar,active_start_time),6),5,2) as active_start_time,
substring(right(‘000000’+convert(varchar,active_end_time),6),1,2)+’:’ + substring(right(‘000000’+convert(varchar,active_end_time),6),3,2) +’:’ + + substring(right(‘000000’+convert(varchar,active_end_time),6),5,2) as active_end_time,
c.freq_subday_interval
into #temp1
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
order by c.schedule_id
–执行计划
select
job_id,
schedule_id,
case when freq_type=1 and freq_interval=0 and freq_subday_type=0 then active_start_time+‘临时执行一次’
when freq_type=4 and freq_subday_type=1 then '【每天】'+active_start_time+'执行一次'
when freq_type=4 and freq_subday_type=4 then '【每天】'+active_start_time+'至'+active_end_time+',每'+convert(varchar,freq_subday_interval)+'分钟执行一次'
when freq_type=4 and freq_subday_type=8 then '【每天】'+active_start_time+'至'+active_end_time+',每'+convert(varchar,freq_subday_interval)+'小时执行一次'
when freq_type=8 and freq_subday_type=1 then '【每周】星期'+freq_subday_type1+active_start_time+'执行一次'
when freq_type=8 and freq_subday_type=4 then '【每周】星期'+freq_subday_type1+active_start_time+'至'+active_end_time+',每'+convert(varchar,freq_subday_interval)+'分钟执行一次'
when freq_type=8 and freq_subday_type=8 then '【每周】星期'+freq_subday_type1+active_start_time+'至'+active_end_time+',每'+convert(varchar,freq_subday_interval)+'小时执行一次'
when freq_type=16 and freq_subday_type=1 then '【每月】第'+cast(freq_interval as varchar(10))+'天的'+active_start_time+'执行一次'
when freq_type=16 and freq_subday_type=4 then '【每月】第'+cast(freq_interval as varchar(10))+'天的'+active_start_time+'至'+active_end_time+',每'+convert(varchar,freq_subday_interval)+'分钟执行一次'
when freq_type=16 and freq_subday_type=8 then '【每月】第'+cast(freq_interval as varchar(10))+'天的'+active_start_time+'至'+active_end_time+',每'+convert(varchar,freq_subday_interval)+'小时执行一次'
when freq_type=32 and freq_interval=8 and freq_subday_type=1 then '【每月】最后一天'+active_start_time+'执行一次'
end as job_plan
into #temp2
from #temp1
select job_id,job_plan,row_number() over(partition by job_id order by schedule_id) as rowm
into #temp3
from #temp2
select
a.job_id,
(
select case when rowm=1 then ‘①’ when rowm=2 then ‘②’ when rowm=3 then ‘③’ when rowm=4 then ‘④’ when rowm=5 then ‘⑤’ end +job_plan+’;’
from #temp3
where job_id=a.job_id
order by rowm for xml path(’’)
) as job_plan
into #temp4
from #temp3 a
where a.rowm=1
–基础数据
select
b.[name] as ’ 作业名称’,
case when b.[enabled]=1 then ‘启用’ else ‘未启用’ end as ‘是否启用’,
isnull(c.job_plan,‘无’) as ‘执行计划’,
a.[step_id] as ’ 步骤编号’,
a.[step_name] as ’ 步骤名称’,
a.[database_name] as ’ 执行数据库名’,
replace(replace(a.[command],char(10),’’),char(13),’;’) as ’ 执行命令’,
case when a.last_run_date=0 then null
else cast(cast(a.last_run_date as char(8))+’ ‘+stuff(stuff(right(‘000000’+cast(a.last_run_time as varchar (6)),6),3,0,’:’),6,0,’:’) as datetime)
end as ‘上次运行时间’,
stuff(stuff(right(‘000000’+ cast (a.last_run_duration as varchar (6)),6),3,0,’:’),6,0,’:’) as ‘执行持续时间’,
case when a.last_run_outcome=0 then ’ 失败’
when a.last_run_outcome=1 then ’ 成功’
when a.last_run_outcome=2 then ’ 重试’
when a.last_run_outcome=3 then ’ 取消’
when a.last_run_outcome=5 then ’ 未知’ end as ’ 上次运行状态’
from [msdb].[dbo].[sysjobsteps] as a
inner join [msdb].[dbo].[sysjobs] as b on b.[job_id] = a.[job_id]
left join #temp4 c on c.[job_id] = a.[job_id]
order by b.[name],a.[step_id]
drop table #temp1
drop table #temp2
drop table #temp3
drop table #temp4
