Cool Tips‎ > ‎Scripting SQL Server‎ > ‎

Generate a descriptive list of SQL Agent jobs

Applicability:

                 SQL Server 2000:        N/A
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Tested
                 SQL Server 2014:        Tested
                 SQL Server 2016:        Tested
                 SQL Server 2017:        Tested

Credits:

Author: Unknown
Date:    10 Jun 2020

Description

Scripting a SQL Agent job using the SSMS interface produces a very raw, and mostly unreadable script, especially where decoding the time schedule is concerned.
This script produces a compact description of the schedule for each job in a very reada ble form. 

Code



USE msdb GO /* Microsoft doesn't provide a way to SELECT the job schedule descriptions. They do provide a stored procedure [msdb.dbo.sp_get_schedule_description] which will return a job schedule description for one job. The below select statement was derived from information in that procedure, and lists all jobs on a server with their name, description and schedule description. */ SELECT j.name , j.description , CASE WHEN j.enabled = 0 THEN 'Disabled' WHEN s.job_id IS NULL THEN 'Unscheduled' WHEN s2.freq_type = 0x1 -- OneTime THEN 'Once on ' + CONVERT( CHAR(10) , CAST( CAST( s2.active_start_date AS VARCHAR ) AS DATETIME ) , 102 -- yyyy.mm.dd ) WHEN s2.freq_type = 0x4 -- Daily THEN 'Daily' WHEN s2.freq_type = 0x8 -- weekly THEN CASE WHEN s2.freq_recurrence_factor = 1 THEN 'Weekly on ' WHEN s2.freq_recurrence_factor > 1 THEN 'Every ' + CAST( s2.freq_recurrence_factor AS VARCHAR ) + ' weeks on ' END + LEFT( CASE WHEN s2.freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END + CASE WHEN s2.freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END + CASE WHEN s2.freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END + CASE WHEN s2.freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END + CASE WHEN s2.freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END + CASE WHEN s2.freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END + CASE WHEN s2.freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END , LEN( CASE WHEN s2.freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END + CASE WHEN s2.freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END + CASE WHEN s2.freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END + CASE WHEN s2.freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END + CASE WHEN s2.freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END + CASE WHEN s2.freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END + CASE WHEN s2.freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END ) - 1 -- LEN() ignores trailing spaces ) WHEN s2.freq_type = 0x10 -- monthly THEN CASE WHEN s2.freq_recurrence_factor = 1 THEN 'Monthly on the ' WHEN s2.freq_recurrence_factor > 1 THEN 'Every ' + CAST( s2.freq_recurrence_factor AS VARCHAR ) + ' months on the ' END + CAST( s2.freq_interval AS VARCHAR ) + CASE WHEN s2.freq_interval IN ( 1, 21, 31 ) THEN 'st' WHEN s2.freq_interval IN ( 2, 22 ) THEN 'nd' WHEN s2.freq_interval IN ( 3, 23 ) THEN 'rd' ELSE 'th' END WHEN s2.freq_type = 0x20 -- monthly relative THEN CASE WHEN s2.freq_recurrence_factor = 1 THEN 'Monthly on the ' WHEN s2.freq_recurrence_factor > 1 THEN 'Every ' + CAST( s2.freq_recurrence_factor AS VARCHAR ) + ' months on the ' END + CASE s2.freq_relative_interval WHEN 0x01 THEN 'first ' WHEN 0x02 THEN 'second ' WHEN 0x04 THEN 'third ' WHEN 0x08 THEN 'fourth ' WHEN 0x10 THEN 'last ' END + CASE s2.freq_interval WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' WHEN 3 THEN 'Tuesday' WHEN 4 THEN 'Wednesday' WHEN 5 THEN 'Thursday' WHEN 6 THEN 'Friday' WHEN 7 THEN 'Saturday' WHEN 8 THEN 'day' WHEN 9 THEN 'week day' WHEN 10 THEN 'weekend day' END WHEN s2.freq_type = 0x40 THEN 'Automatically starts when SQLServerAgent starts.' WHEN s2.freq_type = 0x80 THEN 'Starts whenever the CPUs become idle' ELSE '' END + CASE WHEN j.enabled = 0 THEN '' WHEN s.job_id IS NULL THEN '' WHEN s2.freq_subday_type = 0x1 OR s2.freq_type = 0x1 THEN ' at ' + CASE WHEN s2.active_start_time = 0 THEN '12:00' WHEN s2.active_start_time = 120000 THEN '12:00' WHEN s2.active_start_time < 100000 THEN STUFF( LEFT( CAST ( s2.active_start_time AS VARCHAR ), 3 ) , 2 , 0 , ':' ) WHEN s2.active_start_time < 120000 THEN STUFF( LEFT( CAST ( s2.active_start_time AS VARCHAR ), 4 ) , 3 , 0 , ':' ) WHEN s2.active_start_time < 220000 THEN STUFF( LEFT( CAST ( s2.active_start_time - 120000 AS VARCHAR ), 3 ) , 2 , 0 , ':' ) ELSE STUFF( LEFT( CAST ( s2.active_start_time - 120000 AS VARCHAR ), 4 ) , 3 , 0 , ':' ) END + CASE WHEN s2.active_start_time < 120000 THEN ' AM' ELSE ' PM' END WHEN s2.freq_subday_type IN ( 0x2, 0x4, 0x8 ) THEN ' every ' + CAST( s2.freq_subday_interval AS VARCHAR ) + CASE freq_subday_type WHEN 0x2 THEN ' second' WHEN 0x4 THEN ' minute' WHEN 0x8 THEN ' hour' END + CASE WHEN s2.freq_subday_interval > 1 THEN 's' END ELSE '' END + CASE WHEN j.enabled = 0 THEN '' WHEN s.job_id IS NULL THEN '' WHEN s2.freq_subday_type IN ( 0x2, 0x4, 0x8 ) THEN ' between ' + CASE WHEN s2.active_start_time = 0 THEN '12:00' WHEN s2.active_start_time = 120000 THEN '12:00' WHEN s2.active_start_time < 100000 THEN STUFF( LEFT( CAST ( s2.active_start_time AS VARCHAR ), 3 ) , 2 , 0 , ':' ) WHEN s2.active_start_time < 120000 THEN STUFF( LEFT( CAST ( s2.active_start_time AS VARCHAR ), 4 ) , 3 , 0 , ':' ) WHEN s2.active_start_time < 220000 THEN STUFF( LEFT( CAST ( s2.active_start_time - 120000 AS VARCHAR ), 3 ) , 2 , 0 , ':' ) ELSE STUFF( LEFT( CAST ( s2.active_start_time - 120000 AS VARCHAR ), 4 ) , 3 , 0 , ':' ) END + CASE WHEN s2.active_start_time < 120000 THEN ' AM' ELSE ' PM' END + ' and ' + CASE WHEN s2.active_end_time = 0 THEN '12:00' WHEN s2.active_end_time = 120000 THEN '12:00' WHEN s2.active_end_time < 100000 THEN STUFF( LEFT( CAST ( s2.active_end_time AS VARCHAR ), 3 ) , 2 , 0 , ':' ) WHEN s2.active_end_time < 120000 THEN STUFF( LEFT( CAST ( s2.active_end_time AS VARCHAR ), 4 ) , 3 , 0 , ':' ) WHEN s2.active_end_time < 220000 THEN STUFF( LEFT( CAST ( s2.active_end_time - 120000 AS VARCHAR ), 3 ) , 2 , 0 , ':' ) ELSE STUFF( LEFT( CAST ( s2.active_end_time - 120000 AS VARCHAR ), 4 ) , 3 , 0 , ':' ) END + CASE WHEN s2.active_end_time < 120000 THEN ' AM' ELSE ' PM' END ELSE '' END AS schedule FROM dbo.sysjobs j WITH (nolock) LEFT JOIN dbo.sysjobschedules s WITH (nolock) ON s.job_id = j.job_id LEFT JOIN dbo.sysschedules s2 WITH (nolock) ON s.schedule_id = s2.schedule_id AND s2.enabled = 1 ORDER BY j.name





ċ
Andy Hughes,
Jun 10, 2020, 5:31 PM
Comments