[sourcecode language=”sql”]
use msdb
go
select name, suser_sname(sysjobs.owner_sid) as owner from msdb..sysjobs
go

select sj.name,sjh.run_status,case sjh.run_status
when 1 then ‘Sucess’
when 2 then ‘Retry’
when 3 then ‘Cancelled’
when 4 then ‘In Progress’
when 0 then ‘Failed’
End
as ‘Run Status’,sjh.run_date as ‘Run Date’,
CASE len(run_time)
WHEN 1 THEN cast(’00:00:0′
+ cast(run_time as char) as char (8))
WHEN 2 THEN cast(’00:00:’
+ cast(run_time as char) as char (8))
WHEN 3 THEN cast(’00:0′
+ Left(right(run_time,3),1)
+’:’ + right(run_time,2) as char (8))
WHEN 4 THEN cast(’00:’
+ Left(right(run_time,4),2)
+’:’ + right(run_time,2) as char (8))
WHEN 5 THEN cast(‘0′
+ Left(right(run_time,5),1)
+’:’ + Left(right(run_time,4),2)
+’:’ + right(run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(run_time,6),2)
+’:’ + Left(right(run_time,4),2)
+’:’ + right(run_time,2) as char (8))
END as ‘Start Time’,
CASE len(run_duration)
WHEN 1 THEN cast(’00:00:0′
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast(’00:00:’
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast(’00:0′
+ Left(right(run_duration,3),1)
+’:’ + right(run_duration,2) as char (8))
WHEN 4 THEN cast(’00:’
+ Left(right(run_duration,4),2)
+’:’ + right(run_duration,2) as char (8))
WHEN 5 THEN cast(‘0′
+ Left(right(run_duration,5),1)
+’:’ + Left(right(run_duration,4),2)
+’:’ + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2)
+’:’ + Left(right(run_duration,4),2)
+’:’ + right(run_duration,2) as char (8))
END as ‘Duration’ from msdb..sysjobs sj
join sysjobhistory sjh on sj.job_id=sjh.job_id
where name like ‘NameString%’
and sjh.run_date>=’yyyymmdd’ and sjh.step_id = 0
order by run_duration
[/sourcecode]