在2000和2005中还略有不同

2000中的语句如下：

select J.name 作业名称,P.step_id'步骤编号',P.step_name'步骤名称',P.command'执行命令',
        case freq_type
            when  1 then '只执行一次'
            when  4 then '每日'
            when  8 then '每周'
            when 16 then '每月'   
            when 32 then '每月' 
            when 64 then '当 SQLServerAgent 服务启动时运行'
            when 128 then '计算机空闲时运行'
             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,':') 启动时间,
        case freq_subday_type
            when 4 then '每'+ltrim(str(freq_subday_interval))+'分钟'
            when 8 then '每'+ltrim(str(freq_subday_interval))+'小时'
            else '' end '间隔 ',
        case J.enabled
            when 0 then '禁用'
            when 1 then '启用'
            else '' end '状态',
        case P.last_run_outcome
            when 0 then '失败'
            when 1 then '成功'
            else '' end '上次执行' ,
             P.last_run_duration'执行时间(秒)',
             str(last_run_date)+' '+stuff(stuff(right('000000'+ltrim(str(last_run_time)),6),3,0,':'),6,0,':') '上次启动时间'
  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 启动时间 

2005的SQL语句如下：

select J.name 作业名称,P.step_id'步骤编号',P.step_name'步骤名称',p.SubSystem '步骤类型',P.command'执行命令',
        case freq_type
            when  1 then '只执行一次'
            when  4 then '每日'
            when  8 then '每周'
            when 16 then '每月'   
            when 32 then '每月' 
            when 64 then '当 SQLServerAgent 服务启动时运行'
            when 128 then '计算机空闲时运行'
             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,':') 启动时间,
        case freq_subday_type
            when 4 then '每'+ltrim(str(freq_subday_interval))+'分钟'
            when 8 then '每'+ltrim(str(freq_subday_interval))+'小时'
            else '' end '间隔 ',
        case J.enabled
            when 0 then '禁用'
            when 1 then '启用'
            else '' end '状态',
        case P.last_run_outcome
            when 0 then '失败'
            when 1 then '成功'
            else '' end '上次执行' ,
             P.last_run_duration'执行时间(秒)',
             str(last_run_date)+' '+stuff(stuff(right('000000'+ltrim(str(last_run_time)),6),3,0,':'),6,0,':') '上次启动时间'
  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 启动时间