Embedded Files
Applicability:
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:
Credits:
Author: Unknown
Date: 10 Jun 2020
Description
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
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
Page updated
Google Sites
Report abuse