sqlserver查询所有作业情况

---

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