Display Job Schedule Description (SQL Server 2005)

,

Script modification of existing script to show job information.

Added a column to show whether job schedule is enabled/disabled

-- 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,
CASE
       WHEN msdb.dbo.sysschedules.enabled = 0 THEN 'Disabled'
	   WHEN msdb.dbo.sysschedules.enabled = 1 THEN 'Enabled'
	END
  As [Schedule Enabled]

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

Rate

4.79 (28)

Share

Share

Rate

4.79 (28)