SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Cowboy DBA / Display Job Schedule Description (SQL Server 2005) / Latest Posts

RE: Display Job Schedule Description (SQL Server 2005)

ultimate sql — Wed, 24 Oct 2012 17:51:38 GMT

For those who want to run this script in [b]SQL Server 2000[/b], here is a slightly modified version that doesn't use msdb.dbo.sysschedules. All the scheduling columns were present in msdb.dbo.sysjobschedules system table in SQL Server 2000.[code="sql"]-- Query to display schedules of jobs. -- copied from http://www.sqlservercentral.com/scripts/Maintenance+and+Management/62222/-- posted there by "Cowboy DBA"-- ------------ Revised 3/21/08 John Arnott-- added default "ELSE" to case that would put plural "S" in schedule description. (find comment " -- Added default 3/21/08; John Arnott"-- Revised 6/19/08 John Arnott-- re-coded the time parsing routines. Old code didn't handle first hour of the day (00:04, for instance). -- new code uses division by power of 10 to isolate hour, then minute portion of time, -- then a little trick with REPLICATE to left-fill the minutes with a zero if needed.SELECT @@servername as [Server] , msdb.dbo.sysjobs.name as [JobName] , CASE WHEN msdb.dbo.sysjobs.enabled = 0 THEN 'Disabled' WHEN msdb.dbo.sysjobs.job_id IS NULL THEN 'Unscheduled' WHEN msdb.dbo.sysjobschedules.freq_type = 0x1 -- OneTime THEN 'Once on ' + CONVERT( CHAR(10) , CAST( CAST( msdb.dbo.sysjobschedules.active_start_date AS VARCHAR ) AS DATETIME ) , 102 -- yyyy.mm.dd ) WHEN msdb.dbo.sysjobschedules.freq_type = 0x4 -- Daily THEN 'Daily' WHEN msdb.dbo.sysjobschedules.freq_type = 0x8 -- weekly THEN CASE WHEN msdb.dbo.sysjobschedules.freq_recurrence_factor = 1 THEN 'Weekly on ' WHEN msdb.dbo.sysjobschedules.freq_recurrence_factor > 1 THEN 'Every ' + CAST( msdb.dbo.sysjobschedules.freq_recurrence_factor AS VARCHAR ) + ' weeks on ' END + LEFT( CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END + CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END + CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END + CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END + CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END + CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END + CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END , LEN( CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END + CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END + CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END + CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END + CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END + CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END + CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END ) - 1 -- LEN() ignores trailing spaces ) WHEN msdb.dbo.sysjobschedules.freq_type = 0x10 -- monthly THEN CASE WHEN msdb.dbo.sysjobschedules.freq_recurrence_factor = 1 THEN 'Monthly on the ' WHEN msdb.dbo.sysjobschedules.freq_recurrence_factor > 1 THEN 'Every ' + CAST( msdb.dbo.sysjobschedules.freq_recurrence_factor AS VARCHAR ) + ' months on the ' END + CAST( msdb.dbo.sysjobschedules.freq_interval AS VARCHAR ) + CASE WHEN msdb.dbo.sysjobschedules.freq_interval IN ( 1, 21, 31 ) THEN 'st' WHEN msdb.dbo.sysjobschedules.freq_interval IN ( 2, 22 ) THEN 'nd' WHEN msdb.dbo.sysjobschedules.freq_interval IN ( 3, 23 ) THEN 'rd' ELSE 'th' END WHEN msdb.dbo.sysjobschedules.freq_type = 0x20 -- monthly relative THEN CASE WHEN msdb.dbo.sysjobschedules.freq_recurrence_factor = 1 THEN 'Monthly on the ' WHEN msdb.dbo.sysjobschedules.freq_recurrence_factor > 1 THEN 'Every ' + CAST( msdb.dbo.sysjobschedules.freq_recurrence_factor AS VARCHAR ) + ' months on the ' END + CASE msdb.dbo.sysjobschedules.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 msdb.dbo.sysjobschedules.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 msdb.dbo.sysjobschedules.freq_type = 0x40 THEN 'Automatically starts when SQLServerAgent starts.' WHEN msdb.dbo.sysjobschedules.freq_type = 0x80 THEN 'Starts whenever the CPUs become idle' ELSE '' END + CASE WHEN msdb.dbo.sysjobs.enabled = 0 THEN '' WHEN msdb.dbo.sysjobs.job_id IS NULL THEN '' WHEN msdb.dbo.sysjobschedules.freq_subday_type = 0x1 OR msdb.dbo.sysjobschedules.freq_type = 0x1 THEN ' at ' + Case -- Depends on time being integer to drop right-side digits when(msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000 = 0 then '12' + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100))) + convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100) + ' AM' when (msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000< 10 then convert(char(1),(msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100))) + convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100) + ' AM' when (msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000 < 12 then convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100))) + convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100) + ' AM' when (msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000< 22 then convert(char(1),((msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000) - 12) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100))) + convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100) + ' PM' else convert(char(2),((msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000) - 12) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100))) + convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100) + ' PM' end WHEN msdb.dbo.sysjobschedules.freq_subday_type IN ( 0x2, 0x4, 0x8 ) THEN ' every ' + CAST( msdb.dbo.sysjobschedules.freq_subday_interval AS VARCHAR ) + CASE freq_subday_type WHEN 0x2 THEN ' second' WHEN 0x4 THEN ' minute' WHEN 0x8 THEN ' hour' END + CASE WHEN msdb.dbo.sysjobschedules.freq_subday_interval > 1 THEN 's' ELSE '' -- Added default 3/21/08; John Arnott END ELSE '' END + CASE WHEN msdb.dbo.sysjobs.enabled = 0 THEN '' WHEN msdb.dbo.sysjobs.job_id IS NULL THEN '' WHEN msdb.dbo.sysjobschedules.freq_subday_type IN ( 0x2, 0x4, 0x8 ) THEN ' between ' + Case -- Depends on time being integer to drop right-side digits when(msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000 = 0 then '12' + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100)) + ' AM' when (msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000< 10 then convert(char(1),(msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100)) + ' AM' when (msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000 < 12 then convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100)) + ' AM' when (msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000< 22 then convert(char(1),((msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000) - 12) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100)) + ' PM' else convert(char(2),((msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000) - 12) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100)) + ' PM' end + ' and ' + Case -- Depends on time being integer to drop right-side digits when(msdb.dbo.sysjobschedules.active_end_time % 1000000)/10000 = 0 then '12' + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_end_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysjobschedules.active_end_time % 10000)/100)) + ' AM' when (msdb.dbo.sysjobschedules.active_end_time % 1000000)/10000< 10 then convert(char(1),(msdb.dbo.sysjobschedules.active_end_time % 1000000)/10000) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_end_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysjobschedules.active_end_time % 10000)/100)) + ' AM' when (msdb.dbo.sysjobschedules.active_end_time % 1000000)/10000 < 12 then convert(char(2),(msdb.dbo.sysjobschedules.active_end_time % 1000000)/10000) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_end_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysjobschedules.active_end_time % 10000)/100)) + ' AM' when (msdb.dbo.sysjobschedules.active_end_time % 1000000)/10000< 22 then convert(char(1),((msdb.dbo.sysjobschedules.active_end_time % 1000000)/10000) - 12) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_end_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysjobschedules.active_end_time % 10000)/100)) + ' PM' else convert(char(2),((msdb.dbo.sysjobschedules.active_end_time % 1000000)/10000) - 12) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_end_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysjobschedules.active_end_time % 10000)/100)) + ' PM' end ELSE '' END AS ScheduleFROM msdb.dbo.sysjobs INNER JOIN msdb.dbo.sysjobschedules ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobschedules.job_id --where msdb.dbo.sysjobs.name like '%DCT%'order by 2[/code]

RE: Display Job Schedule Description (SQL Server 2005)

rjaypriya — Wed, 16 Mar 2011 00:38:43 GMT

awesome work!!

RE: Display Job Schedule Description (SQL Server 2005)

john.arnott — Thu, 19 Jun 2008 12:03:05 GMT

Here's another fix. The time displays in the original script don't handle values in the first hour of the day. For instance, a job set to start at four minutes after midnight, which SQL Server records with msdb.dbo.sysschedules.active_start_time=400, gets reported as 4:00 AM. I've re-coded the three places that parse out the time values, using division by a power of ten to isolate the hour or minute portion, then a little trick with REPLICATE to left fill the minutes with a zero. This technique is not an obvious solution here, but may be handy to remember for other LZF applications. Here's the complete script with my modifications:[code]-- Query to display schedules of jobs. -- copied from http://www.sqlservercentral.com/scripts/Maintenance+and+Management/62222/-- posted there by "Cowboy DBA"-- ------------ Revised 3/21/08 John Arnott-- added default "ELSE" to case that would put plural "S" in schedule description. (find comment " -- Added default 3/21/08; John Arnott"-- Revised 6/19/08 John Arnott-- re-coded the time parsing routines. Old code didn't handle first hour of the day (00:04, for instance). -- new code uses division by power of 10 to isolate hour, then minute portion of time, -- then a little trick with REPLICATE to left-fill the minutes with a zero if needed.SELECT @@servername as [Server] , msdb.dbo.sysjobs.name as [JobName] , CASE WHEN msdb.dbo.sysjobs.enabled = 0 THEN 'Disabled' WHEN msdb.dbo.sysjobs.job_id IS NULL THEN 'Unscheduled' WHEN msdb.dbo.sysschedules.freq_type = 0x1 -- OneTime THEN 'Once on ' + CONVERT( CHAR(10) , CAST( CAST( msdb.dbo.sysschedules.active_start_date AS VARCHAR ) AS DATETIME ) , 102 -- yyyy.mm.dd ) WHEN msdb.dbo.sysschedules.freq_type = 0x4 -- Daily THEN 'Daily' WHEN msdb.dbo.sysschedules.freq_type = 0x8 -- weekly THEN CASE WHEN msdb.dbo.sysschedules.freq_recurrence_factor = 1 THEN 'Weekly on ' WHEN msdb.dbo.sysschedules.freq_recurrence_factor > 1 THEN 'Every ' + CAST( msdb.dbo.sysschedules.freq_recurrence_factor AS VARCHAR ) + ' weeks on ' END + LEFT( CASE WHEN msdb.dbo.sysschedules.freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END , LEN( CASE WHEN msdb.dbo.sysschedules.freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END ) - 1 -- LEN() ignores trailing spaces ) WHEN msdb.dbo.sysschedules.freq_type = 0x10 -- monthly THEN CASE WHEN msdb.dbo.sysschedules.freq_recurrence_factor = 1 THEN 'Monthly on the ' WHEN msdb.dbo.sysschedules.freq_recurrence_factor > 1 THEN 'Every ' + CAST( msdb.dbo.sysschedules.freq_recurrence_factor AS VARCHAR ) + ' months on the ' END + CAST( msdb.dbo.sysschedules.freq_interval AS VARCHAR ) + CASE WHEN msdb.dbo.sysschedules.freq_interval IN ( 1, 21, 31 ) THEN 'st' WHEN msdb.dbo.sysschedules.freq_interval IN ( 2, 22 ) THEN 'nd' WHEN msdb.dbo.sysschedules.freq_interval IN ( 3, 23 ) THEN 'rd' ELSE 'th' END WHEN msdb.dbo.sysschedules.freq_type = 0x20 -- monthly relative THEN CASE WHEN msdb.dbo.sysschedules.freq_recurrence_factor = 1 THEN 'Monthly on the ' WHEN msdb.dbo.sysschedules.freq_recurrence_factor > 1 THEN 'Every ' + CAST( msdb.dbo.sysschedules.freq_recurrence_factor AS VARCHAR ) + ' months on the ' END + CASE msdb.dbo.sysschedules.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 msdb.dbo.sysschedules.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 msdb.dbo.sysschedules.freq_type = 0x40 THEN 'Automatically starts when SQLServerAgent starts.' WHEN msdb.dbo.sysschedules.freq_type = 0x80 THEN 'Starts whenever the CPUs become idle' ELSE '' END + CASE WHEN msdb.dbo.sysjobs.enabled = 0 THEN '' WHEN msdb.dbo.sysjobs.job_id IS NULL THEN '' WHEN msdb.dbo.sysschedules.freq_subday_type = 0x1 OR msdb.dbo.sysschedules.freq_type = 0x1 THEN ' at ' + Case -- Depends on time being integer to drop right-side digits when(msdb.dbo.sysschedules.active_start_time % 1000000)/10000 = 0 then '12' + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) + convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100) + ' AM' when (msdb.dbo.sysschedules.active_start_time % 1000000)/10000< 10 then convert(char(1),(msdb.dbo.sysschedules.active_start_time % 1000000)/10000) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) + convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100) + ' AM' when (msdb.dbo.sysschedules.active_start_time % 1000000)/10000 < 12 then convert(char(2),(msdb.dbo.sysschedules.active_start_time % 1000000)/10000) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) + convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100) + ' AM' when (msdb.dbo.sysschedules.active_start_time % 1000000)/10000< 22 then convert(char(1),((msdb.dbo.sysschedules.active_start_time % 1000000)/10000) - 12) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) + convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100) + ' PM' else convert(char(2),((msdb.dbo.sysschedules.active_start_time % 1000000)/10000) - 12) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) + convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100) + ' PM' end WHEN msdb.dbo.sysschedules.freq_subday_type IN ( 0x2, 0x4, 0x8 ) THEN ' every ' + CAST( msdb.dbo.sysschedules.freq_subday_interval AS VARCHAR ) + CASE freq_subday_type WHEN 0x2 THEN ' second' WHEN 0x4 THEN ' minute' WHEN 0x8 THEN ' hour' END + CASE WHEN msdb.dbo.sysschedules.freq_subday_interval > 1 THEN 's' ELSE '' -- Added default 3/21/08; John Arnott END ELSE '' END + CASE WHEN msdb.dbo.sysjobs.enabled = 0 THEN '' WHEN msdb.dbo.sysjobs.job_id IS NULL THEN '' WHEN msdb.dbo.sysschedules.freq_subday_type IN ( 0x2, 0x4, 0x8 ) THEN ' between ' + Case -- Depends on time being integer to drop right-side digits when(msdb.dbo.sysschedules.active_start_time % 1000000)/10000 = 0 then '12' + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100)) + ' AM' when (msdb.dbo.sysschedules.active_start_time % 1000000)/10000< 10 then convert(char(1),(msdb.dbo.sysschedules.active_start_time % 1000000)/10000) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100)) + ' AM' when (msdb.dbo.sysschedules.active_start_time % 1000000)/10000 < 12 then convert(char(2),(msdb.dbo.sysschedules.active_start_time % 1000000)/10000) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100)) + ' AM' when (msdb.dbo.sysschedules.active_start_time % 1000000)/10000< 22 then convert(char(1),((msdb.dbo.sysschedules.active_start_time % 1000000)/10000) - 12) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100)) + ' PM' else convert(char(2),((msdb.dbo.sysschedules.active_start_time % 1000000)/10000) - 12) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100)) + ' PM' end + ' and ' + Case -- Depends on time being integer to drop right-side digits when(msdb.dbo.sysschedules.active_end_time % 1000000)/10000 = 0 then '12' + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100)) + ' AM' when (msdb.dbo.sysschedules.active_end_time % 1000000)/10000< 10 then convert(char(1),(msdb.dbo.sysschedules.active_end_time % 1000000)/10000) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100)) + ' AM' when (msdb.dbo.sysschedules.active_end_time % 1000000)/10000 < 12 then convert(char(2),(msdb.dbo.sysschedules.active_end_time % 1000000)/10000) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100)) + ' AM' when (msdb.dbo.sysschedules.active_end_time % 1000000)/10000< 22 then convert(char(1),((msdb.dbo.sysschedules.active_end_time % 1000000)/10000) - 12) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100)) + ' PM' else convert(char(2),((msdb.dbo.sysschedules.active_end_time % 1000000)/10000) - 12) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100)) + ' PM' end ELSE '' END AS ScheduleFROM msdb.dbo.sysjobs INNER JOIN msdb.dbo.sysjobschedules ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobschedules.job_id INNER JOIN msdb.dbo.sysschedules ON msdb.dbo.sysjobschedules.schedule_id = msdb.dbo.sysschedules.schedule_id--where msdb.dbo.sysjobs.name like '%DCT%'order by 2[/code][i]edited to put in rtrim() to keep spacing between time and AM/PM consistent[/i][i]edited (again) to comment out my "where" clause that no one else would want to use ;)[/i]

RE: Display Job Schedule Description (SQL Server 2005)

john.arnott — Fri, 21 Mar 2008 18:47:52 GMT

Cool script. I did have to make a correction/addition to avoid trying to string together a NULL in the middle of the schedule description. The case statement that would put an "s" after a freq_subday_interval greater than 1 needs a default zero-length string. I added this ELSE line (it's near line 150 in the original script......)[quote] + CASE WHEN msdb.dbo.sysschedules.freq_subday_interval > 1 THEN 's' ELSE '' -- Added default 3/21/08; John Arnott END[/quote]

RE: Display Job Schedule Description (SQL Server 2005)

Lowry Kozlowski — Fri, 21 Mar 2008 06:52:45 GMT

Thanks for the script Cowboy. It also helped show me that a job that I thought was running was not scheduled! Oops! With over 200 jobs scheduled it was hard to keep track of!:w00t:

Display Job Schedule Description (SQL Server 2005)

Cowboy DBA — Fri, 22 Feb 2008 10:30:43 GMT

Comments posted to this topic are about the item [B]Display Job Schedule Description (SQL Server 2005)[/B]