Here is a very simple script to see all your SQL Agent Jobs and their corresponding schedules, Enjoy!

USE msdb;
GO

SELECT DISTINCT J.job_id
    , J.name AS JobName
    , COUNT(st.step_id) OVER (PARTITION BY st.job_id) AS StepCount
    , J.enabled
    , ISNULL(C.name, '--') AS ScheduleName
    , CAST(CASE C.freq_type
            WHEN 1
                THEN 'Once'
            WHEN 4
                THEN 'Daily'
            WHEN 8
                THEN 'Weekly'
            WHEN 16
                THEN 'Monthly'
            WHEN 32
                THEN 'Monthly'
            WHEN 64
                THEN 'Agent startup'
            ELSE 'On-Demand'
            END AS NVARCHAR(MAX)) AS ScheduleType
    , CAST(CASE C.freq_type
            WHEN 1
                THEN 'Occurs Once'
            WHEN 4
                THEN IIF(C.freq_subday_type = 1, 'Occurs once a Day', CONCAT (
                            'Occurs every '
                            , C.freq_subday_interval
                            , ' '
                            , CASE freq_subday_type
                                WHEN 2
                                    THEN ' Seconds'
                                WHEN 4
                                    THEN 'Minutes'
                                WHEN 8
                                    THEN 'Hours'
                                END
                            ))
            WHEN 8
                THEN CONCAT (
                        'Occurs on '
                        , LTRIM(RTRIM(SUBSTRING(Weekly.Days, 2, LEN(Weekly.Days))))
                        )
            WHEN 16
                THEN CONCAT ('Occurs on the ', C.freq_interval, ' of every ', C.freq_recurrence_factor, ' month(s)')
            WHEN 32
                THEN CONCAT ('Occurs every ', RI.PartOfMonth, ' ', MIF.Frequency, ' of every ', C.freq_recurrence_factor, ' month(s)')
            WHEN 64
                THEN 'Occurs on SQL Agent Startup'
            ELSE '--'
            END AS NVARCHAR(MAX)) AS Recurrence
    , CASE 
        WHEN C.active_start_time IS NULL
            THEN '--'
        WHEN C.active_start_time = 0
            AND freq_type = 4
            AND freq_subday_type <> 1
            THEN '<-- See Recurrence Interval'
        WHEN C.active_start_time = 0
            THEN '00:00:00'
        ELSE IIF(LEN(C.active_start_time) = 6, CONCAT (
                    SUBSTRING(CAST(C.active_start_time AS VARCHAR(6)), 1, 2)
                    , ':'
                    , SUBSTRING(CAST(C.active_start_time AS VARCHAR(6)), 3, 2)
                    , ':'
                    , SUBSTRING(CAST(C.active_start_time AS VARCHAR(6)), 5, 2)
                    ), CONCAT (
                    SUBSTRING(CAST(C.active_start_time AS VARCHAR(6)), 1, 1)
                    , ':'
                    , SUBSTRING(CAST(C.active_start_time AS VARCHAR(6)), 2, 2)
                    , ':'
                    , SUBSTRING(CAST(C.active_start_time AS VARCHAR(6)), 4, 2)
                    ))
        END AS ScheduleTime
FROM dbo.sysjobs AS J
LEFT JOIN dbo.sysjobsteps st
    ON st.job_id = J.job_id
LEFT JOIN dbo.sysjobschedules AS SC
    ON SC.job_id = J.job_id
LEFT JOIN dbo.sysschedules AS C
    ON C.schedule_id = SC.schedule_id
OUTER APPLY (
    SELECT W.D AS [Days]
    FROM (
        SELECT N', ' + wb.[DayOfWeek]
        FROM (
            VALUES 
                  (1, 'Sunday')
                , (2, 'Monday')
                , (4, 'Tuesday')
                , (8, 'Wednesday')
                , (16, 'Thursday')
                , (32, 'Friday')
                , (64, 'Saturday')
            ) wb(DayBit, [DayOfWeek])
        WHERE wb.DayBit & C.freq_interval <> 0
        FOR XML PATH('')
        ) W(D)
    ) AS Weekly
LEFT JOIN (
    SELECT R.ID
        , R.PartOfMonth
    FROM (
        VALUES 
              (1, 'First')
            , (2, 'Second')
            , (4, 'Third')
            , (8, 'Fourth')
            , (16, 'Last')
        ) R(ID, PartOfMonth)
    ) RI
    ON RI.ID = C.freq_relative_interval
        AND C.freq_type = 32
LEFT JOIN (
    SELECT F.ID
        , F.Frequency
    FROM (
        VALUES (1,'Sunday')
            , (2,'Monday')
            , (3,'Tuesday')
            , (4,'Wednesday')
            , (5, 'Thursday')
            , (6, 'Friday')
            , (7, 'Saturday')
            , (8, 'Day')
            , (9, 'Weekday'
            ),(10,'Weekend')
        ) F(ID, Frequency)
    ) MIF
    ON MIF.ID = C.freq_interval
        AND C.freq_type = 32;

Leave a comment