Generate Job Schedule Descriptions

,

Microsoft doesn't provide a way toSELECT 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.

This script's select statement was derived from information in that procedure, and lists

all jobs on a server with their name, description and schedule description.

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 s.freq_type = 0x1 -- OneTime
           THEN
               'Once on '
             + CONVERT(
                          CHAR(10)
                        , CAST( CAST( s.active_start_date AS VARCHAR ) AS DATETIME )
                        , 102 -- yyyy.mm.dd
                      )
       WHEN s.freq_type = 0x4 -- Daily
           THEN 'Daily'
       WHEN s.freq_type = 0x8 -- weekly
           THEN
               CASE
                   WHEN s.freq_recurrence_factor = 1
                       THEN 'Weekly on '
                   WHEN s.freq_recurrence_factor > 1
                       THEN 'Every '
                          + CAST( s.freq_recurrence_factor AS VARCHAR )
                          + ' weeks on '
               END
             + LEFT(
                         CASE WHEN s.freq_interval &  1 =  1 THEN 'Sunday, '    ELSE '' END
                       + CASE WHEN s.freq_interval &  2 =  2 THEN 'Monday, '    ELSE '' END
                       + CASE WHEN s.freq_interval &  4 =  4 THEN 'Tuesday, '   ELSE '' END
                       + CASE WHEN s.freq_interval &  8 =  8 THEN 'Wednesday, ' ELSE '' END
                       + CASE WHEN s.freq_interval & 16 = 16 THEN 'Thursday, '  ELSE '' END
                       + CASE WHEN s.freq_interval & 32 = 32 THEN 'Friday, '    ELSE '' END
                       + CASE WHEN s.freq_interval & 64 = 64 THEN 'Saturday, '  ELSE '' END
                     , LEN(
                                CASE WHEN s.freq_interval &  1 =  1 THEN 'Sunday, '    ELSE '' END
                              + CASE WHEN s.freq_interval &  2 =  2 THEN 'Monday, '    ELSE '' END
                              + CASE WHEN s.freq_interval &  4 =  4 THEN 'Tuesday, '   ELSE '' END
                              + CASE WHEN s.freq_interval &  8 =  8 THEN 'Wednesday, ' ELSE '' END
                              + CASE WHEN s.freq_interval & 16 = 16 THEN 'Thursday, '  ELSE '' END
                              + CASE WHEN s.freq_interval & 32 = 32 THEN 'Friday, '    ELSE '' END
                              + CASE WHEN s.freq_interval & 64 = 64 THEN 'Saturday, '  ELSE '' END
                          ) - 1  -- LEN() ignores trailing spaces
                   )
       WHEN s.freq_type = 0x10 -- monthly
           THEN
               CASE
                   WHEN s.freq_recurrence_factor = 1
                       THEN 'Monthly on the '
                   WHEN s.freq_recurrence_factor > 1
                       THEN 'Every '
                          + CAST( s.freq_recurrence_factor AS VARCHAR )
                          + ' months on the '
               END
             + CAST( s.freq_interval AS VARCHAR )
             + CASE
                   WHEN s.freq_interval IN ( 1, 21, 31 ) THEN 'st'
                   WHEN s.freq_interval IN ( 2, 22     ) THEN 'nd'
                   WHEN s.freq_interval IN ( 3, 23     ) THEN 'rd'
                   ELSE 'th'
               END
       WHEN s.freq_type = 0x20 -- monthly relative
           THEN
               CASE
                   WHEN s.freq_recurrence_factor = 1
                       THEN 'Monthly on the '
                   WHEN s.freq_recurrence_factor > 1
                       THEN 'Every '
                          + CAST( s.freq_recurrence_factor AS VARCHAR )
                          + ' months on the '
               END
             + CASE s.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 s.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 s.freq_type = 0x40
           THEN 'Automatically starts when SQLServerAgent starts.'
       WHEN s.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 s.freq_subday_type = 0x1 OR s.freq_type = 0x1
           THEN ' at '
             + CASE
                   WHEN s.active_start_time =      0 THEN '12:00'
                   WHEN s.active_start_time = 120000 THEN '12:00'
                   WHEN s.active_start_time < 100000
                       THEN STUFF(
                                     LEFT( CAST ( s.active_start_time AS VARCHAR ), 3 )
                                   , 2
                                   , 0
                                   , ':'
                                 )
                   WHEN s.active_start_time < 120000
                       THEN STUFF(
                                     LEFT( CAST ( s.active_start_time AS VARCHAR ), 4 )
                                   , 3
                                   , 0
                                   , ':'
                                 )
                   WHEN s.active_start_time < 220000
                       THEN STUFF(
                                     LEFT( CAST ( s.active_start_time - 120000 AS VARCHAR ), 3 )
                                   , 2
                                   , 0
                                   , ':'
                                 )
                   ELSE STUFF(
                                 LEFT( CAST ( s.active_start_time - 120000 AS VARCHAR ), 4 )
                               , 3
                               , 0
                               , ':'
                             )
               END
             + CASE
                   WHEN s.active_start_time < 120000 THEN ' AM'
                   ELSE ' PM'
               END
       WHEN s.freq_subday_type IN ( 0x2, 0x4, 0x8 )
           THEN ' every '
             + CAST( s.freq_subday_interval AS VARCHAR )
             + CASE freq_subday_type
                   WHEN 0x2 THEN ' second'
                   WHEN 0x4 THEN ' minute'
                   WHEN 0x8 THEN ' hour'
               END
             + CASE
                   WHEN s.freq_subday_interval > 1 THEN 's'
               END
       ELSE ''
   END
 + CASE
       WHEN j.enabled = 0 THEN ''
       WHEN s.job_id IS NULL THEN ''
       WHEN s.freq_subday_type IN ( 0x2, 0x4, 0x8 )
           THEN ' between '
             + CASE
                   WHEN s.active_start_time =      0 THEN '12:00'
                   WHEN s.active_start_time = 120000 THEN '12:00'
                   WHEN s.active_start_time < 100000
                       THEN STUFF(
                                     LEFT( CAST ( s.active_start_time AS VARCHAR ), 3 )
                                   , 2
                                   , 0
                                   , ':'
                                 )
                   WHEN s.active_start_time < 120000
                       THEN STUFF(
                                     LEFT( CAST ( s.active_start_time AS VARCHAR ), 4 )
                                   , 3
                                   , 0
                                   , ':'
                                 )
                   WHEN s.active_start_time < 220000
                       THEN STUFF(
                                     LEFT( CAST ( s.active_start_time - 120000 AS VARCHAR ), 3 )
                                   , 2
                                   , 0
                                   , ':'
                                 )
                   ELSE STUFF(
                                 LEFT( CAST ( s.active_start_time - 120000 AS VARCHAR ), 4 )
                               , 3
                               , 0
                               , ':'
                             )
               END
             + CASE
                   WHEN s.active_start_time < 120000 THEN ' AM'
                   ELSE ' PM'
               END
             + ' and '
             + CASE
                   WHEN s.active_end_time =      0 THEN '12:00'
                   WHEN s.active_end_time = 120000 THEN '12:00'
                   WHEN s.active_end_time < 100000
                       THEN STUFF(
                                     LEFT( CAST ( s.active_end_time AS VARCHAR ), 3 )
                                   , 2
                                   , 0
                                   , ':'
                                 )
                   WHEN s.active_end_time < 120000
                       THEN STUFF(
                                     LEFT( CAST ( s.active_end_time AS VARCHAR ), 4 )
                                   , 3
                                   , 0
                                   , ':'
                                 )
                   WHEN s.active_end_time < 220000
                       THEN STUFF(
                                     LEFT( CAST ( s.active_end_time - 120000 AS VARCHAR ), 3 )
                                   , 2
                                   , 0
                                   , ':'
                                 )
                   ELSE STUFF(
                                 LEFT( CAST ( s.active_end_time - 120000 AS VARCHAR ), 4 )
                               , 3
                               , 0
                               , ':'
                             )
               END
             + CASE
                   WHEN s.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
                                                AND s.enabled = 1

ORDER BY
   j.name

Rate

3 (7)

Share

Share

Rate

3 (7)