Display Job Schedule Description (SQL Server 2005)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Cowboy DBA » Display Job Schedule Description (SQL Server...

Display Job Schedule Description (SQL Server 2005)

Rate Topic

Display Mode

Topic Options

Author

Message

Cowboy DBA

Posted Friday, February 22, 2008 10:30 AM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 91, Visits: 1,988

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

Post #459205

Lowry Kozlowski

Posted Friday, March 21, 2008 6:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:59 AM
Points: 137, Visits: 398

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

Post #472762

john.arnott

Posted Friday, March 21, 2008 6:47 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 7:59 PM
Points: 1,491, Visits: 3,010

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......)

+ CASE
WHEN msdb.dbo.sysschedules.freq_subday_interval > 1 THEN 's'
ELSE '' -- Added default 3/21/08; John Arnott
END

Post #473164

john.arnott

Posted Thursday, June 19, 2008 12:03 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 7:59 PM
Points: 1,491, Visits: 3,010

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:

-- 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 Schedule

FROM         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

edited to put in rtrim() to keep spacing between time and AM/PM consistent
edited (again) to comment out my "where" clause that no one else would want to use ;)

Post #520097

rjaypriya

Posted Wednesday, March 16, 2011 12:38 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 9:38 AM
Points: 2, Visits: 119

awesome work!!

Post #1078805

ultimate sql

Posted Wednesday, October 24, 2012 5:51 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, February 10, 2013 10:00 PM
Points: 194, Visits: 180

For those who want to run this script in SQL Server 2000, 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.

-- 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 Schedule

FROM         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

Fahim Ahmed
Knowledge is a journey, not a destiny

Post #1376733

« Prev Topic | Next Topic »

Permissions