SQLServer JOBに関する実行状態を問い合わせる文

SELECT
    [jop].[job_id] AS '       '
   ,[jop].[name] AS '    '
   ,[dp].[name] AS '     '
   ,[cat].[name] AS '    '
   ,[jop].[description] AS '    '
   , CASE [jop].[enabled]
        WHEN 1 THEN ' '
        WHEN 0 THEN ' '
      END AS '    '
   ,[jop].[date_created] AS '      '
   ,[jop].[date_modified] AS '        '
   ,[sv].[name] AS '         '
   ,[step].[step_id] AS '      '
   ,[step].[step_name] AS '    '
   , CASE
        WHEN [sch].[schedule_uid] IS NULL THEN ' '
          ELSE ' '
      END AS '       '
   ,[sch].[schedule_uid] AS '          '
   ,[sch].[name] AS '           '
   , CASE [jop].[delete_level]
        WHEN 0 THEN '   '
        WHEN 1 THEN '     '
        WHEN 2 THEN '     '
        WHEN 3 THEN '     '
      END AS '        '
FROM [msdb].[dbo].[sysjobs] AS [jop]
LEFT JOIN [msdb].[sys].[servers] AS [sv]
         ON [jop].[originating_server_id] = [sv].[server_id]
LEFT JOIN [msdb].[dbo].[syscategories] AS [cat]
         ON [jop].[category_id] = [cat].[category_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [step]
         ON [jop].[job_id] = [step].[job_id]
            AND [jop].[start_step_id] = [step].[step_id]
LEFT JOIN [msdb].[sys].[database_principals] AS [dp]
         ON [jop].[owner_sid] = [dp].[sid]
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [jsch]
         ON [jop].[job_id] = [jsch].[job_id]
LEFT JOIN [msdb].[dbo].[sysschedules] AS [sch]
         ON [jsch].[schedule_id] = [sch].[schedule_id]
ORDER BY [jop].[name]

SELECT
    [job].[job_id] AS '       '
   ,[job].[name] AS '    '
   , CASE WHEN [jobh].[run_date] IS NULL
               OR [jobh].[run_time] IS NULL THEN NULL
          ELSE CAST ( CAST ([jobh].[run_date] AS CHAR ( 8 )) + ' '
               + STUFF(STUFF( RIGHT ( '000000'
                                   + CAST ([jobh].[run_time] AS VARCHAR ( 6 )), 6 ),
                             3 , 0 , ':' ), 6 , 0 , ':' ) AS DATETIME)
     END AS '      '
   , CASE [jobh].[run_status]
      WHEN 0 THEN '  '
      WHEN 1 THEN '  '
      WHEN 2 THEN '  '
      WHEN 3 THEN '  '
      WHEN 4 THEN '    '
     END AS '      '
   ,STUFF(STUFF( RIGHT ( '000000' + CAST ([jobh].[run_duration] AS VARCHAR ( 6 )), 6 ),
                 3 , 0 , ':' ), 6 , 0 , ':' ) AS '        '
   ,[jobh].[message] AS '        '
   , CASE [jsch].[NextRunDate]
      WHEN 0 THEN NULL
       ELSE CAST ( CAST ([jsch].[NextRunDate] AS CHAR ( 8 )) + ' '
            + STUFF(STUFF( RIGHT ( '000000'
                                + CAST ([jsch].[NextRunTime] AS VARCHAR ( 6 )),
                                6 ), 3 , 0 , ':' ), 6 , 0 , ':' ) AS DATETIME)
     END AS '      '
FROM [msdb].[dbo].[sysjobs] AS [job]
LEFT JOIN (
             SELECT
                [job_id]
               , MIN ([next_run_date]) AS [NextRunDate]
               , MIN ([next_run_time]) AS [NextRunTime]
             FROM [msdb].[dbo].[sysjobschedules]
             GROUP BY [job_id]
          ) AS [jsch]
         ON [job].[job_id] = [jsch].[job_id]
LEFT JOIN (
             SELECT
                [job_id]
               ,[run_date]
               ,[run_time]
               ,[run_status]
               ,[run_duration]
               ,[message]
               ,ROW_NUMBER() OVER ( PARTITION BY [job_id] ORDER BY [run_date] DESC , [run_time] DESC ) AS RowNumber
             FROM [msdb].[dbo].[sysjobhistory]
             WHERE [step_id] = 0
          ) AS [jobh]
     ON [job].[job_id] = [jobh].[job_id]
        AND [jobh].[RowNumber] = 1
ORDER BY [job].[name]

SELECT
    [job].[job_id] AS '       '
   ,[job].[name] AS '    '
   ,[jstep].[step_uid] AS '       '
   ,[jstep].[step_id] AS '    '
   ,[jstep].[step_name] AS '    '
   ,CASE [jstep].[subsystem]
      WHEN 'ActiveScripting' THEN 'ActiveX Script'
      WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
      WHEN 'PowerShell' THEN 'PowerShell'
      WHEN 'Distribution' THEN 'Replication Distributor'
      WHEN 'Merge' THEN 'Replication Merge'
      WHEN 'QueueReader' THEN 'Replication Queue Reader'
      WHEN 'Snapshot' THEN 'Replication Snapshot'
      WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
      WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
      WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
      WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
      WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
      ELSE [jstep].[subsystem]
    END AS '      '
   ,CASE
        WHEN [px].[name] IS NULL THEN 'SQL SERVER      '
        ELSE [px].[name]
    END AS '      '
   ,[jstep].[database_name] AS '      '
   ,[jstep].[command] AS '    '
   ,CASE [jstep].[on_success_action]
      WHEN 1 THEN '         '
      WHEN 2 THEN '         '
      WHEN 3 THEN '     '
      WHEN 4
      THEN '    : '
           + QUOTENAME(CAST([jstep].[on_success_step_id] AS VARCHAR(3))) + ' '
           + [sOSSTP].[step_name]
    END AS '       '
   ,[jstep].[retry_attempts] AS '        '
   ,[jstep].[retry_interval] AS '        (  )'
   ,CASE [jstep].[on_fail_action]
      WHEN 1 THEN '         '
      WHEN 2 THEN '         '
      WHEN 3 THEN '     '
      WHEN 4
      THEN '    : '
           + QUOTENAME(CAST([jstep].[on_fail_step_id] AS VARCHAR(3))) + ' '
           + [sOFSTP].[step_name]
    END AS '       '
FROM [msdb].[dbo].[sysjobsteps] AS [jstep]
INNER JOIN [msdb].[dbo].[sysjobs] AS [job]
        ON [jstep].[job_id] = [job].[job_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]
        ON [jstep].[job_id] = [sOSSTP].[job_id]
           AND [jstep].[on_success_step_id] = [sOSSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]
        ON [jstep].[job_id] = [sOFSTP].[job_id]
           AND [jstep].[on_fail_step_id] = [sOFSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysproxies] AS [px]--      
        ON [jstep].[proxy_id] = [px].[proxy_id]
ORDER BY [job].[name], [jstep].[step_id]

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]
ORDER BY [job].[name], [jstep].[step_id]