https://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/ The following query gives us the Job Level Setup and Configuration information which is also found in the SQL Server Agent Job Properties window in SSMS. SELECT [sJOB].[job_id] AS [JobID] , [sJOB].[name] AS [JobName] , [sDBP].[name] AS [JobOwner] , [sCAT].[name] AS [JobCategory] , [sJOB].[description] AS [JobDescription] , CASE [sJOB].[enabled] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END AS [IsEnabled] , [sJOB].[date_created] AS [JobCreatedOn] , [sJOB].[date_modified] AS [JobLastModifiedOn] , [sSVR].[name] AS [OriginatingServerName] , [sJSTP].[step_id] AS [JobStartStepNo] , [sJSTP].[step_name] AS [JobStartStepName] , CASE WHEN [sSCH].[schedule_uid] IS NULL THEN 'No' ELSE 'Yes' END AS [IsScheduled] , [sSCH].[schedule_uid] AS [JobScheduleID] , [sSCH].[name] AS [JobScheduleName] , CASE [sJOB].[delete_level] WHEN 0 THEN 'Never' WHEN 1 THEN 'On Success' WHEN 2 THEN 'On Failure' WHEN 3 THEN 'On Completion' END AS [JobDeletionCriterion] 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 [JobName]
