블로그 "공감"

select job_id, name, step_id, step_name, subsystem, command, database_name,

       last_run_outcome, last_run_date,

       (substring(last_run_time, 1, 2) + ':' + substring(last_run_time, 3, 2) + ':' + substring(last_run_time, 5, 2)) as last_run_time,

       (substring(last_run_duration, 1, 2) + ':' + substring(last_run_duration, 3, 2) + ':' + substring(last_run_duration, 5, 2)) as last_run_duration,

       last_outcome_message

from (

       select a.job_id, a.name, c.step_id, c.step_name, c.subsystem, c.command, c.database_name,

             (case b.last_run_outcome

                                                                                                             when 0 then '실패'

                           when 1 then '성공'

                                                                                                             when 3 then '취소' end) as last_run_outcome,

             b.last_run_date,

             right('S000000' + convert(varchar(8), b.last_run_time), 6) as last_run_time,

             right('S000000' + convert(varchar(8), b.last_run_duration), 6) as last_run_duration,

                                                      b.last_outcome_message

       from msdb.dbo.sysjobs a

             left join msdb.dbo.sysjobservers b on a.job_id = b.job_id

             left join msdb.dbo.sysjobsteps c on a.job_id = c.job_id

       where a.enabled = 1

) as O

order by name, step_id

다음도 참고 : select * from sysjobs_view