Job schedules out of MSDB

 /*

Summary:

Returns all SQL Server Agent jobs and their associated schedules,

including frequency settings, active date/time windows, and a

human-readable schedule description.

Useful for auditing backup plans, maintenance plans, and other

scheduled SQL Agent jobs.

*/

USE msdb

SELECT

    -- SQL Agent job name

    j.name AS [Job Name],

    -- Schedule assigned to the job

    s.name AS [Schedule Name],

    -- Indicates whether the schedule is enabled (1) or disabled (0)

    s.enabled AS [ScheduleEnabled],

    -- Convert integer HHMMSS value to readable time format

    RIGHT('0' + CAST(s.active_start_time / 10000 AS VARCHAR(2)), 2) + ':' +

    RIGHT('0' + CAST((s.active_start_time / 100) % 100 AS VARCHAR(2)), 2) + ':' +

    RIGHT('0' + CAST(s.active_start_time % 100 AS VARCHAR(2)), 2)

        AS ActiveStartTime,

    -- Convert integer HHMMSS value to readable time format

    RIGHT('0' + CAST(s.active_end_time / 10000 AS VARCHAR(2)), 2) + ':' +

    RIGHT('0' + CAST((s.active_end_time / 100) % 100 AS VARCHAR(2)), 2) + ':' +

    RIGHT('0' + CAST(s.active_end_time % 100 AS VARCHAR(2)), 2)

        AS ActiveEndTime,

    -- Translate SQL Agent frequency codes to friendly descriptions

    CASE

        WHEN s.freq_type = 1   THEN 'One Time'

        WHEN s.freq_type = 4   THEN 'Daily'

        WHEN s.freq_type = 8   THEN 'Weekly'

        WHEN s.freq_type = 16  THEN 'Monthly'

        WHEN s.freq_type = 32  THEN 'Monthly, Relative'

        WHEN s.freq_type = 64  THEN 'When SQL Server Agent Starts'

        WHEN s.freq_type = 128 THEN 'When Computer Is Idle'

        ELSE 'Unknown'

    END AS FrequencyDescription,

    -- Numeric frequency type (daily, weekly, monthly, etc.)

    s.freq_type AS [Frequency Type],

    -- Frequency-specific interval value

    s.freq_interval AS [Frequency Interval],

    -- Defines subday scheduling type (hours, minutes, seconds)

    s.freq_subday_type AS [Subday Type],

    -- Interval for subday execution frequency

    s.freq_subday_interval AS [Subday Interval],

    -- Used by relative monthly schedules

    s.freq_relative_interval AS [Relative Interval],

    -- Recurrence factor (e.g., every N weeks/months)

    s.freq_recurrence_factor AS [Recurrence Factor],

    -- First date the schedule becomes active

    s.active_start_date AS [Active Start Date],

    -- Last date the schedule remains active

    s.active_end_date AS [Active End Date]

FROM msdb.dbo.sysjobs j

    -- Link jobs to schedules

    INNER JOIN msdb.dbo.sysjobschedules js

        ON j.job_id = js.job_id

    -- Retrieve schedule details

    INNER JOIN msdb.dbo.sysschedules s

        ON js.schedule_id = s.schedule_id

-- Sort by job and schedule name for readability

ORDER BY

    j.name,

    s.name;