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,
max(SJA.run_requested_date) as run_requested_date,
max(SJA.next_scheduled_run_date) as next_scheduled_run_date
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
LEFT JOIN msdb.dbo.sysjobactivity SJA ON SJA.job_id = JOB.job_id
group by
Job.name,
CASE
WHEN Job.enabled = 1 THEN ‘Enable’
ELSE ‘Disable’
END ,
JOB.description,
SCH.name,
CASE
WHEN SCH.enabled = 1 THEN ‘Enable’
WHEN SCH.enabled = 0 THEN ‘Disable’
ELSE ‘Not Schedule’
END,
SCH.active_start_date,
SCH.active_end_date,
SCH.active_start_time,
SCH.active_end_time