Get SQL Server Agent Jobs List with Schedule in SQL Server

 

The list of SQL Server Agent Jobs with schedules is provided in this article. System tables from the MSDB database will be used.

·                Sys.SysJobs

·                Sys.SysSchedules

·                Sys.SysJobSchedules

 

To retrieve a list of SQL Server Agent Jobs with schedules, use the script below.


USE msdb
GO 


SELECT Job.name        AS JobName,
       

CASE

         WHEN Job.enabled = 1 THEN 'Enable'

         ELSE 'Disable'

       END             AS JobStatus,

       JOB.description AS Job_Description,

       SCH.name        AS ScheduleName,

       CASE

         WHEN SCH.enabled = 1 THEN 'Enable'

         WHEN SCH.enabled = 0 THEN 'Disable'

         ELSE 'Not Schedule'

       END             AS ScheduleStatus,

       SCH.active_start_date,

       SCH.active_end_date,

       SCH.active_start_time,

       SCH.active_end_time

 FROM   dbo.sysjobs JOB

      LEFT JOIN dbo.sysjobschedules JS

               ON Job.job_id = JS.job_id

       LEFT JOIN dbo.sysschedules SCH

               ON JS.schedule_id = SCH.schedule_id