統(tǒng)計(jì)MSSQL中的Job情況代碼

2005中還略有不同
    2000中的語(yǔ)句如下：
    select J.name 作業(yè)名稱,P.step_id'步驟編號(hào)',P.step_name'步驟名稱',P.command'執(zhí)行命令',
     case freq_type
     when 1 then '只執(zhí)行一次'
     when 4 then '每日'
     when 8 then '每周'
     when 16 then '每月'
     when 32 then '每月'
     when 64 then '當(dāng) SQLServerAgent 服務(wù)啟動(dòng)時(shí)運(yùn)行'
     when 128 then '計(jì)算機(jī)空閑時(shí)運(yùn)行'
     else '' end 頻率,
     case freq_type
     when 8 then case when freq_interval&1 = 1 then '星期日' else '' end +
     case when freq_interval&2 = 2 then '星期一' else '' end +
     case when freq_interval&4 = 4 then '星期二' else '' end +
     case when freq_interval&8 = 8 then '星期三' else '' end +
     case when freq_interval&16=16 then '星期四' else '' end +
     case when freq_interval&32=32 then '星期五' else '' end +
     case when freq_interval&64=64 then '星期六' else '' end
     when 16 then '第'+ltrim(str(freq_interval))+'天'
     else '' end 指定 ,
     stuff(stuff(right('000000'+ltrim(str(active_start_time)),6),3,0,':'),6,0,':') 啟動(dòng)時(shí)間,
     case freq_subday_type
     when 4 then '每'+ltrim(str(freq_subday_interval))+'分鐘'
     when 8 then '每'+ltrim(str(freq_subday_interval))+'小時(shí)'
     else '' end '間隔 ',
     case J.enabled
     when 0 then '禁用'
     when 1 then '啟用'
     else '' end '狀態(tài)',
     case P.last_run_outcome
     when 0 then '失敗'
     when 1 then '成功'
     else '' end '上次執(zhí)行' ,
     P.last_run_duration'執(zhí)行時(shí)間(秒)',
     str(last_run_date)+' '+stuff(stuff(right('000000'+ltrim(str(last_run_time)),6),3,0,':'),6,0,':') '上次啟動(dòng)時(shí)間'
     from msdb.dbo.sysjobschedules S
     inner join msdb.dbo.sysjobs J on S.job_id = J.job_id
     inner join msdb.dbo.sysjobsteps P on S.job_id = P.job_id
     order by 啟動(dòng)時(shí)間
    2005的SQL語(yǔ)句如下：
    select J.name 作業(yè)名稱,P.step_id'步驟編號(hào)',P.step_name'步驟名稱',p.SubSystem '步驟類型',P.command'執(zhí)行命令',
     case freq_type
     when 1 then '只執(zhí)行一次'
     when 4 then '每日'
     when 8 then '每周'
     when 16 then '每月'
     when 32 then '每月'
     when 64 then '當(dāng) SQLServerAgent 服務(wù)啟動(dòng)時(shí)運(yùn)行'
     when 128 then '計(jì)算機(jī)空閑時(shí)運(yùn)行'
     else '' end 頻率,
     case freq_type
     when 8 then case when freq_interval&1 = 1 then '星期日' else '' end +
     case when freq_interval&2 = 2 then '星期一' else '' end +
     case when freq_interval&4 = 4 then '星期二' else '' end +
     case when freq_interval&8 = 8 then '星期三' else '' end +
     case when freq_interval&16=16 then '星期四' else '' end +
     case when freq_interval&32=32 then '星期五' else '' end +
     case when freq_interval&64=64 then '星期六' else '' end
     when 16 then '第'+ltrim(str(freq_interval))+'天'
     else '' end 指定 ,
     stuff(stuff(right('000000'+ltrim(str(active_start_time)),6),3,0,':'),6,0,':') 啟動(dòng)時(shí)間,
     case freq_subday_type
     when 4 then '每'+ltrim(str(freq_subday_interval))+'分鐘'
     when 8 then '每'+ltrim(str(freq_subday_interval))+'小時(shí)'
     else '' end '間隔 ',
     case J.enabled
     when 0 then '禁用'
     when 1 then '啟用'
     else '' end '狀態(tài)',
     case P.last_run_outcome
     when 0 then '失敗'
     when 1 then '成功'
     else '' end '上次執(zhí)行' ,
     P.last_run_duration'執(zhí)行時(shí)間(秒)',
     str(last_run_date)+' '+stuff(stuff(right('000000'+ltrim(str(last_run_time)),6),3,0,':'),6,0,':') '上次啟動(dòng)時(shí)間'
     from msdb.dbo.sysschedules S
    inner join msdb.dbo.sysjobschedules SCH on SCH.schedule_id=S.schedule_id
     inner join msdb.dbo.sysjobs J on SCH.job_id = J.job_id
     inner join msdb.dbo.sysjobsteps P on SCH.job_id = P.job_id
     order by 啟動(dòng)時(shí)間