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;