统计MSSQL中的Job情况的方法在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 启动时间