SQL SERVER JOB 管理脚本收集

-- 作业信息

SELECT  [sJOB].[job_id] AS [作业ID] ,
        [sJOB].[name] AS [作业名称] ,
        [sDBP].[name] AS [作业创建者] ,
        [sCAT].[name] AS [作业种类] ,
        [sJOB].[description] AS [作业描述] ,
        CASE [sJOB].[enabled]
          WHEN 1 THEN '已启用'
          WHEN 0 THEN '未启用'
        END AS [是否启用] ,--
        [sJOB].[date_created] AS [作业创建日期] ,
        [sJOB].[date_modified] AS [作业最后修改日期] ,
        [sSVR].[name] AS [作业运行服务器] ,
        [sJSTP].[step_id] AS [作业起始步骤] ,
        [sJSTP].[step_name] AS [步骤名称] ,
        CASE WHEN [sSCH].[schedule_uid] IS NULL THEN '否'
             ELSE '是'
        END AS [是否分布式作业] ,
        [sSCH].[schedule_uid] AS [分布式作业ID] ,
        [sSCH].[name] AS [用户定义名称] ,
        CASE [sJOB].[delete_level]
          WHEN 0 THEN '不删除'
          WHEN 1 THEN '成功后删除'
          WHEN 2 THEN '失败后删除'
          WHEN 3 THEN '完成时删除'
        END AS [完成时删除作业级别]
FROM    [msdb].[dbo].[sysjobs] AS [sJOB]
        LEFT JOIN [msdb].[sys].[servers] AS [sSVR] ON [sJOB].[originating_server_id] = [sSVR].[server_id]
        LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT] ON [sJOB].[category_id] = [sCAT].[category_id]
        LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP] ON [sJOB].[job_id] = [sJSTP].[job_id]
                                                           AND [sJOB].[start_step_id] = [sJSTP].[step_id]
        LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP] ON [sJOB].[owner_sid] = [sDBP].[sid]
        LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]
        LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH] ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
ORDER BY [作业名称]

-- 查询作业执行情况

SELECT  [sJOB].[job_id] AS [作业ID] ,
        [sJOB].[name] AS [作业名] ,
        CASE WHEN [sJOBH].[run_date] IS NULL
                  OR [sJOBH].[run_time] IS NULL THEN NULL
             ELSE CAST(CAST([sJOBH].[run_date] AS CHAR(8)) + ' '
                  + STUFF(STUFF(RIGHT('000000'
                                      + CAST([sJOBH].[run_time] AS VARCHAR(6)),
                                      6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
        END AS [最近执行时间] ,
        CASE [sJOBH].[run_status]
          WHEN 0 THEN '失败'
          WHEN 1 THEN '成功'
          WHEN 2 THEN '重试'
          WHEN 3 THEN '取消'
          WHEN 4 THEN '正在运行' -- In Progress
        END AS [最近执行状态] ,
        STUFF(STUFF(RIGHT('000000'
                          + CAST([sJOBH].[run_duration] AS VARCHAR(6)), 6), 3,
                    0, ':'), 6, 0, ':') AS [LastRunDuration (HH:MM:SS)] ,
        [sJOBH].[message] AS [最近运行状态信息] ,
        CASE [sJOBSCH].[NextRunDate]
          WHEN 0 THEN NULL
          ELSE CAST(CAST([sJOBSCH].[NextRunDate] AS CHAR(8)) + ' '
               + STUFF(STUFF(RIGHT('000000'
                                   + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),
                                   6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
        END AS [下次运行时间]
FROM    [msdb].[dbo].[sysjobs] AS [sJOB]
        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 [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[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 [sJOBH] ON [sJOB].[job_id] = [sJOBH].[job_id]
                                  AND [sJOBH].[RowNumber] = 1
ORDER BY [作业名]

--查看作业步骤信息

SELECT  [sJOB].[job_id] AS [作业ID] ,
        [sJOB].[name] AS [作业名] ,
        [sJSTP].[step_uid] AS [步骤ID] ,
        [sJSTP].[step_id] AS [步骤序号] ,
        [sJSTP].[step_name] AS [步骤名] ,
        CASE [sJSTP].[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 sJSTP.subsystem
        END AS [作业子系统类型] ,
        [sPROX].[name] AS [作业运行账号] ,
        [sJSTP].[database_name] AS [执行数据库名] ,
        [sJSTP].[command] AS [执行命令] ,
        CASE [sJSTP].[on_success_action]
          WHEN 1 THEN 'Quit the job reporting success'
          WHEN 2 THEN 'Quit the job reporting failure'
          WHEN 3 THEN 'Go to the next step'
          WHEN 4
          THEN 'Go to Step: '
               + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3)))
               + ' ' + [sOSSTP].[step_name]
        END AS [执行成功后反应] ,
        [sJSTP].[retry_attempts] AS [失败时的重试次数] ,
        [sJSTP].[retry_interval] AS [重试间的等待时间(Minutes)] ,
        CASE [sJSTP].[on_fail_action]
          WHEN 1 THEN 'Quit the job reporting success'
          WHEN 2 THEN 'Quit the job reporting failure'
          WHEN 3 THEN 'Go to the next step'
          WHEN 4
          THEN 'Go to Step: '
               + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3)))
               + ' ' + [sOFSTP].[step_name]
        END AS [执行失败后反映]
FROM    [msdb].[dbo].[sysjobsteps] AS [sJSTP]
        INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB] ON [sJSTP].[job_id] = [sJOB].[job_id]
        LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP] ON [sJSTP].[job_id] = [sOSSTP].[job_id]
                                                            AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
        LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP] ON [sJSTP].[job_id] = [sOFSTP].[job_id]
                                                            AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
        LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX] ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
ORDER BY [作业名] ,
        [步骤序号]

-- CONVERT() 

CONVERT(data_type(length),data_to_be_converted,style)
data_type(length) 规定目标数据类型（带有可选的长度）。data_to_be_converted 含有需要转换的值。style 规定日期/时间的输出格式。
可以使用的 style 值：
Style ID	Style 格式
100 或者 0	mon dd yyyy hh:miAM （或者 PM）
101	mm/dd/yy
102	yy.mm.dd
103	dd/mm/yy
104	dd.mm.yy
105	dd-mm-yy
106	dd mon yy
107	Mon dd, yy
108	hh:mm:ss
109 或者 9	mon dd yyyy hh:mi:ss:mmmAM（或者 PM）
110	mm-dd-yy
111	yy/mm/dd
112	yymmdd
113 或者 13	dd mon yyyy hh:mm:ss:mmm(24h)
114	hh:mi:ss:mmm(24h)
120 或者 20	yyyy-mm-dd hh:mi:ss(24h)
121 或者 21	yyyy-mm-dd hh:mi:ss.mmm(24h)
126	yyyy-mm-ddThh:mm:ss.mmm（没有空格）
130	dd mon yyyy hh:mi:ss:mmmAM
131	dd/mm/yy hh:mi:ss:mmmAM