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