SELECT
[job].[job_id] AS '作業唯一標識符'
,[job].[name] AS '作業名稱'
,[jstep].[step_uid] AS '作業步驟唯一標識符'
,[jstep].[step_id] AS '步驟編號'
,[jstep].[step_name] AS '步驟名稱'
,CASE [jstep].[last_run_outcome]
WHEN 0 THEN '失敗'
WHEN 1 THEN '成功'
WHEN 2 THEN '重試'
WHEN 3 THEN '取消'
WHEN 5 THEN '未知'
END AS '上次運行狀態'
,STUFF(STUFF(RIGHT('000000'
+ CAST([jstep].[last_run_duration] AS VARCHAR(6)), 6), 3,
0, ':'), 6, 0, ':') AS [LastRunDuration (HH:MM:SS)]
,[jstep].[last_run_retries] AS '上次運行重復執行次數'
,CASE [jstep].[last_run_date]
WHEN 0 THEN NULL
ELSE CAST(CAST([jstep].[last_run_date] AS CHAR(8)) + ' '
+ STUFF(STUFF(RIGHT('000000'
+ CAST([jstep].[last_run_time] AS VARCHAR(6)),
6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
END AS '上次運行時間'
FROM [msdb].[dbo].[sysjobsteps] AS [jstep]
INNER JOIN [msdb].[dbo].[sysjobs] AS [job]
ON [jstep].[job_id] = [job].[job_id]
WHERE job.enabled = 1
ORDER BY [jstep].[last_run_outcome],[job].[name], [jstep].[step_id]