数据库作业查询SQL

--查询操作某数据库的作业step
SELECT [job].[job_id] AS '作业唯一标识符',
       [job].[name] AS '作业名称',
       [jobstep].[step_uid] AS '作业步骤唯一标识符',
       [jobstep].[step_id] AS '步骤编号',
       [jobstep].[step_name] AS '步骤名称',
       CASE [jobstep].[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([jobstep].[last_run_duration] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS [LastRunDuration (HH:MM:SS)],
       [jobstep].[last_run_retries] AS '上次运行重复执行次数',
       CASE [jobstep].[last_run_date]
           WHEN 0 THEN
               NULL
           ELSE
               CAST(CAST([jobstep].[last_run_date] AS CHAR(8)) + ' '
                    + STUFF(
                               STUFF(RIGHT('000000' + CAST([jobstep].[last_run_time] AS VARCHAR(6)), 6), 3, 0, ':'),
                               6,
                               0,
                               ':'
                           ) AS DATETIME)
       END AS '上次运行时间'
FROM [msdb].[dbo].[sysjobsteps] AS [jobstep]
    INNER JOIN [msdb].[dbo].[sysjobs] AS [job]
        ON [jobstep].[job_id] = [job].[job_id]
WHERE [jobstep].[database_name] = 'ShopBussiness' --数据库名称
--  AND UPPER([jobstep].[command]) LIKE UPPER('%执行的命令名称%')
ORDER BY [job].[name],
         [jobstep].[step_id];

--根据job_id查询作业详细
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]
WHERE jop.job_id = 'E07C7E1D-32AA-4863-A3BC-C5A3D5C748B3' --根据job_id查询作业详细
ORDER BY [jop].[name];