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]
更多可链接http://www.xuebuyuan.com/1406004.html