Script to Get Job Details in SQL 2005

This script will get job details for all the jobs

Applies to

• SQL Server 2000
• SQL Server 2005
• SQL Server 2008

Script

select b.name,a.step_name,c.message,
case c.run_status when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
when 4 then 'In progress'
else 'NO STATUS' END as 'status'
,substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),c.run_time), 6), 1, 2)
+ ':'+ substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), c.run_time), 6) ,3 ,2)
+ ':'+ substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),c.run_time), 6) ,5 ,2) as run_time
,substring (right (stuff (' ', 1, 1, '00000000') + convert(varchar(8),c.run_date), 8), 1, 4)
+ '/' + substring (right (stuff (' ', 1, 1, '00000000') + convert(varchar(8), c.run_date), 8),5 ,2)
+ '/' + substring (right (stuff (' ', 1, 1, '00000000') + convert(varchar(8),c.run_date), 8),7 ,2) as run_date
from msdb..sysjobsteps a, msdb..sysjobhistory c,msdb..sysjobs b
where a.step_id=c.step_id and a.job_id=c.job_id
and a.last_run_date=c.run_date and a.last_run_time=c.run_time and a.job_id=b.job_id
order by run_date, run_time

Usage

Copy the script and execute in query analyzer or management studio

Sample Output