/*
SQL Server Agent Schedule Decoder
By Michael Abair
Created: 2009-12-16
*/
SELECT SJ.name AS job_name,
SJ.enabled AS is_job_enabled,
SS.enabled AS is_schedule_enabled
/*, ISNULL(SJ.description, '') as job_desc*/,
SS.name AS schedule_name,
CASE freq_type
WHEN 1
THEN 'Occurs on ' + STUFF(RIGHT(active_start_date, 4), 3, 0, '/') + '/' + LEFT(active_start_date, 4) + ' at '
+ REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000'
+ CAST(active_start_time AS VARCHAR(10)),
6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME) /* hh:mm:ss 24H */, 9),
14), ':000', ' ') /* HH:mm:ss:000AM/PM then replace the :000 with space.*/
WHEN 4
THEN 'Occurs every ' + CAST(freq_interval AS VARCHAR(10)) + ' day(s) ' + CASE freq_subday_type
WHEN 1
THEN 'at '
+ LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000'
+ CAST(active_start_time AS VARCHAR(10)),
6), 3, 0, ':'),
6, 0, ':'), 8) AS DATETIME), 9),
14), ':000', ' '))
WHEN 2
THEN 'every '
+ CAST(freq_subday_interval AS VARCHAR(10))
+ ' second(s)'
WHEN 4
THEN 'every '
+ CAST(freq_subday_interval AS VARCHAR(10))
+ ' minute(s)'
WHEN 8
THEN 'every '
+ CAST(freq_subday_interval AS VARCHAR(10))
+ ' hour(s)'
ELSE ''
END
+ CASE WHEN freq_subday_type IN (2, 4, 8) /* repeat seconds/mins/hours */
THEN ' between '
+ LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000'
+ CAST(active_start_time AS VARCHAR(10)),
6), 3, 0, ':'),
6, 0, ':'), 8) AS DATETIME), 9),
14), ':000', ' ')) + ' and '
+ LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000'
+ CAST(active_end_time AS VARCHAR(10)),
6), 3, 0, ':'),
6, 0, ':'), 8) AS DATETIME), 9),
14), ':000', ' '))
ELSE ''
END
WHEN 8
THEN 'Occurs every ' + CAST(freq_recurrence_factor AS VARCHAR(10)) + ' week(s) on '
+ REPLACE(CASE WHEN freq_interval & 1 = 1 THEN 'Sunday, '
ELSE ''
END + CASE WHEN freq_interval & 2 = 2 THEN 'Monday, '
ELSE ''
END + CASE WHEN freq_interval & 4 = 4 THEN 'Tuesday, '
ELSE ''
END + CASE WHEN freq_interval & 8 = 8 THEN 'Wednesday, '
ELSE ''
END + CASE WHEN freq_interval & 16 = 16 THEN 'Thursday, '
ELSE ''
END + CASE WHEN freq_interval & 32 = 32 THEN 'Friday, '
ELSE ''
END + CASE WHEN freq_interval & 64 = 64 THEN 'Saturday, '
ELSE ''
END + '|', ', |', ' ') /* get rid of trailing comma */
+ CASE freq_subday_type
WHEN 1
THEN 'at '
+ LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000'
+ CAST(active_start_time AS VARCHAR(10)),
6), 3, 0, ':'),
6, 0, ':'), 8) AS DATETIME), 9),
14), ':000', ' '))
WHEN 2 THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' second(s)'
WHEN 4 THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' minute(s)'
WHEN 8 THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' hour(s)'
ELSE ''
END + CASE WHEN freq_subday_type IN (2, 4, 8) /* repeat seconds/mins/hours */
THEN ' between '
+ LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000'
+ CAST(active_start_time AS VARCHAR(10)),
6), 3, 0, ':'),
6, 0, ':'), 8) AS DATETIME), 9),
14), ':000', ' ')) + ' and '
+ LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000'
+ CAST(active_end_time AS VARCHAR(10)),
6), 3, 0, ':'),
6, 0, ':'), 8) AS DATETIME), 9),
14), ':000', ' '))
ELSE ''
END
WHEN 16
THEN 'Occurs every ' + CAST(freq_recurrence_factor AS VARCHAR(10)) + ' month(s) on ' + 'day '
+ CAST(freq_interval AS VARCHAR(10)) + ' of that month ' + CASE freq_subday_type
WHEN 1
THEN 'at '
+ LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000'
+ CAST(active_start_time AS VARCHAR(10)),
6), 3, 0, ':'),
6, 0, ':'), 8) AS DATETIME), 9),
14), ':000', ' '))
WHEN 2
THEN 'every '
+ CAST(freq_subday_interval AS VARCHAR(10))
+ ' second(s)'
WHEN 4
THEN 'every '
+ CAST(freq_subday_interval AS VARCHAR(10))
+ ' minute(s)'
WHEN 8
THEN 'every '
+ CAST(freq_subday_interval AS VARCHAR(10))
+ ' hour(s)'
ELSE ''
END
+ CASE WHEN freq_subday_type IN (2, 4, 8) /* repeat seconds/mins/hours */
THEN ' between '
+ LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000'
+ CAST(active_start_time AS VARCHAR(10)),
6), 3, 0, ':'),
6, 0, ':'), 8) AS DATETIME), 9),
14), ':000', ' ')) + ' and '
+ LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000'
+ CAST(active_end_time AS VARCHAR(10)),
6), 3, 0, ':'),
6, 0, ':'), 8) AS DATETIME), 9),
14), ':000', ' '))
ELSE ''
END
WHEN 32
THEN 'Occurs ' + CASE freq_relative_interval
WHEN 1 THEN 'every first '
WHEN 2 THEN 'every second '
WHEN 4 THEN 'every third '
WHEN 8 THEN 'every fourth '
WHEN 16 THEN 'on the last '
END + CASE 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 'weekday'
WHEN 10 THEN 'weekend'
END + ' of every ' + CAST(freq_recurrence_factor AS VARCHAR(10)) + ' month(s) '
+ CASE freq_subday_type
WHEN 1
THEN 'at '
+ LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000'
+ CAST(active_start_time AS VARCHAR(10)),
6), 3, 0, ':'),
6, 0, ':'), 8) AS DATETIME), 9),
14), ':000', ' '))
WHEN 2 THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' second(s)'
WHEN 4 THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' minute(s)'
WHEN 8 THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' hour(s)'
ELSE ''
END + CASE WHEN freq_subday_type IN (2, 4, 8) /* repeat seconds/mins/hours */
THEN ' between '
+ LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000'
+ CAST(active_start_time AS VARCHAR(10)),
6), 3, 0, ':'),
6, 0, ':'), 8) AS DATETIME), 9),
14), ':000', ' ')) + ' and '
+ LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000'
+ CAST(active_end_time AS VARCHAR(10)),
6), 3, 0, ':'),
6, 0, ':'), 8) AS DATETIME), 9),
14), ':000', ' '))
ELSE ''
END
WHEN 64 THEN 'Runs when the SQL Server Agent service starts'
WHEN 128 THEN 'Runs when the computer is idle'
END AS [Description],
CASE freq_type
WHEN 1 THEN 'One Time'
WHEN 4 THEN CASE freq_subday_type
WHEN 1 THEN 'Daily'
WHEN 2 THEN 'Secondly'
WHEN 4 THEN 'Minutely'
WHEN 8 THEN 'Hourly'
ELSE ''
END
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly, relative to freq_interval'
WHEN 64 THEN 'Runs when the SQL Server Agent service starts'
WHEN 128 THEN 'Runs when the computer is idle'
END AS [Freq_Type],
CASE freq_subday_type
WHEN 1 THEN 'At the specified time'
WHEN 2 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' Second(s)'
WHEN 4 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' Minute(s)'
WHEN 8 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' Hour(s)'
ELSE ''
END AS freq_subday_type,
CASE WHEN freq_type = 4 /* (daily) */ THEN 'Every ' + CAST(freq_interval AS VARCHAR(10)) + ' Day(s)'
WHEN freq_type = 8 /* (weekly) */
THEN REPLACE(CASE WHEN freq_interval & 1 = 1 THEN 'Sunday, '
ELSE ''
END + CASE WHEN freq_interval & 2 = 2 THEN 'Monday, '
ELSE ''
END + CASE WHEN freq_interval & 4 = 4 THEN 'Tuesday, '
ELSE ''
END + CASE WHEN freq_interval & 8 = 8 THEN 'Wednesday, '
ELSE ''
END + CASE WHEN freq_interval & 16 = 16 THEN 'Thursday, '
ELSE ''
END + CASE WHEN freq_interval & 32 = 32 THEN 'Friday, '
ELSE ''
END + CASE WHEN freq_interval & 64 = 64 THEN 'Saturday, '
ELSE ''
END + '|', ', |', ' ') /* get rid of trailing comma */
WHEN freq_type = 16 THEN 'On Day ' + CAST(freq_interval AS VARCHAR(10)) + ' of Every Month'
WHEN freq_type = 32 /* (monthly) */ THEN 'Every ' + CASE 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 'Weekday'
WHEN 10 THEN 'Weekend day'
END
ELSE ''
END AS [freq_interval],
CASE WHEN freq_type = 8 /* (weekly) */ THEN 'Every ' + CAST(freq_recurrence_factor AS VARCHAR(10)) + ' Week(s)'
WHEN freq_type = 16 /* (monthly) */
THEN 'Every ' + CAST(freq_recurrence_factor AS VARCHAR(10)) + ' Month(s)'
WHEN freq_type = 32 /* (monthly relative) */
THEN 'Every ' + CAST(freq_recurrence_factor AS VARCHAR(10)) + ' Week(s)'
ELSE ''
END AS freq_recurrence_factor,
LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000'
+ CAST(active_start_time AS VARCHAR(10)),
6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9),
14), ':000', ' ')) AS starttime,
LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000'
+ CAST(active_end_time AS VARCHAR(10)),
6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9),
14), ':000', ' ')) AS endtime,
STUFF(STUFF(active_start_date, 5, 0, '/'), 8, 0, '/') AS schedule_start_date,
STUFF(STUFF(active_end_date, 5, 0, '/'), 8, 0, '/') AS schedule_end_date
FROM msdb.dbo.sysjobs SJ
INNER JOIN msdb.dbo.sysjobschedules SJS ON SJ.job_id = SJS.job_id
INNER JOIN msdb.dbo.sysschedules SS ON SJS.schedule_id = SS.schedule_id