Documenting Job/steps

[dmcmunn]

Here's a query to assist in documenting jobs and job steps for purposes of review and analysis the spreadsheet of your choice.

select j.job_id
, j.name
, j.description
, j.enabled
, j.start_step_id
, j.date_created
, j.date_modified
, s.step_id
, s.step_name
, s.subsystem
, s.command
, case s.on_success_action
when 1 then 'quit with success'
when 2 then 'quit with failure'
when 3 then 'goto next step'
when 4 then 'goto step ' + cast(s.on_success_step_id as varchar(5))
else 'success action : ' + cast(s.on_success_action as varchar(3))
end as on_success
, case s.on_fail_action
when 1 then 'quit with success'
when 2 then 'quit with failure'
when 3 then 'goto next step'
when 4 then 'goto step ' + cast(s.on_fail_step_id as varchar(5))
else 'fail action : ' + cast(s.on_fail_action as varchar(3))
end as on_fail
, s.server
, s.database_name
, left(right( '00000000' + cast(s.last_run_date as varchar(8)), 8), 4)
+ '-'
+ substring(right('00000000' + cast(s.last_run_date as varchar(8)), 8),5,2)
+ '-'
+ right(right('00000000' + cast(s.last_run_date as varchar(8)), 8), 2) as last_run_date
, left(right('00000000' + cast(s.last_run_time as varchar(8)), 8), 4)
+ ':'
+ substring(right('00000000' + cast(s.last_run_time as varchar(8)), 8),5,2)
+ ':'
+ right(right('00000000' + cast(s.last_run_time as varchar(8)), 8), 2) as last_run_tod
, s.last_run_duration

from msdb..sysjobsteps s
inner join msdb..sysjobs j
on s.job_id = j.job_id
order by j.name, j.job_id, s.step_id