About Job Setup and Configuration Information in SQL Server

Hello everyone,

In this article, I will try to give information about getting information about Job setup and configuration in SQL Server.

If you are using Job in SQL Server, you may want to learn about its installation and configuration.

You can also get this information using the code below.

SELECT @@SERVICENAME AS ServiceName,
       [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];

When you run the above code, you will see a result similar to the one below.

As you can see, information about Job setup and configuration has been brought.

Good luck to everyone in business and life.