Below you will find the code to get information about the jobs. Simply select the text and copy to the Clipboard.
–I honestly don’t remember where I picked this code up…so here are a few places in could have
–https://dba.stackexchange.com/questions/148321/list-jobs-schedules-and-next-scheduled-run-datetimes
–https://stackoverflow.com/questions/6538851/sql-scheduled-job-query-duration-of-last-runs/33109187
SELECT job.Name
, CASE job.Enabled
WHEN 1 THEN ‘Yes’
WHEN 0 THEN ‘No’
END AS ‘Enabled’
, scd.schedule_id
, CASE job.enabled
WHEN 1 THEN ‘Yes’
WHEN 0 THEN ‘No’
END AS ‘Scheduled Enabled’
, CASE scd.freq_type
WHEN 1 THEN ‘Once’
WHEN 4 THEN ‘Daily’
WHEN 8 THEN ‘Weekly’
WHEN 16 THEN ‘Monthly’
WHEN 32 THEN ‘Monthly relative’
WHEN 64 THEN ‘When SQLServer Agent starts’
END AS ‘Frequency’
, CASE NextRunDate
WHEN 0 THEN NULL
ELSE SUBSTRING(CONVERT(VARCHAR(15), NextRunDate), 1, 4) + ‘/’ +
SUBSTRING(CONVERT(VARCHAR(15), NextRunDate), 5, 2) + ‘/’ +
SUBSTRING(CONVERT(VARCHAR(15), NextRunDate), 7, 2)
END AS ‘NextRunDate’
, CASE LEN(NextRunTime)
WHEN 1 THEN CAST(’00:00:0′ + RIGHT(NextRunTime, 2) AS CHAR(8))
WHEN 2 THEN CAST(’00:00:’ + RIGHT(NextRunTime, 2) AS CHAR(8))
WHEN 3 THEN CAST(’00:0′
+ LEFT(RIGHT(NextRunTime, 3), 1)
+ ‘:’ + RIGHT(NextRunTime, 2) AS CHAR(8))
WHEN 4 THEN CAST(’00:’
+ LEFT(RIGHT(NextRunTime, 4), 2)
+ ‘:’ + RIGHT(NextRunTime, 2) AS CHAR(8))
WHEN 5 THEN CAST(‘0’
+ LEFT(RIGHT(NextRunTime, 5), 1)
+ ‘:’ + LEFT(RIGHT(NextRunTime, 4), 2)
+ ‘:’ + RIGHT(NextRunTime, 2) AS CHAR(8))
WHEN 6 THEN CAST(LEFT(RIGHT(NextRunTime, 6), 2)
+ ‘:’ + LEFT(RIGHT(NextRunTime, 4), 2)
+ ‘:’ + RIGHT(NextRunTime, 2) AS CHAR(8))
END AS ‘NextRunTime’
, CASE LEN(run_duration)
WHEN 1 THEN CAST(’00:00:0′
+ CAST(run_duration AS CHAR) AS CHAR(8))
WHEN 2 THEN CAST(’00:00:’
+ CAST(run_duration AS CHAR) AS CHAR(8))
WHEN 3 THEN CAST(’00:0′
+ LEFT(RIGHT(run_duration, 3), 1)
+ ‘:’ + RIGHT(run_duration, 2) AS CHAR(8))
WHEN 4 THEN CAST(’00:’
+ LEFT(RIGHT(run_duration, 4), 2)
+ ‘:’ + RIGHT(run_duration, 2) AS CHAR(8))
WHEN 5 THEN CAST(‘0’
+ LEFT(RIGHT(run_duration, 5), 1)
+ ‘:’ + LEFT(RIGHT(run_duration, 4), 2)
+ ‘:’ + RIGHT(run_duration, 2) AS CHAR(8))
WHEN 6 THEN CAST(LEFT(RIGHT(run_duration, 6), 2)
+ ‘:’ + LEFT(RIGHT(run_duration, 4), 2)
+ ‘:’ + RIGHT(run_duration, 2) AS CHAR(8))
END AS ‘MaxRunDuration’
, CASE (scd.freq_subday_interval)
WHEN 0 THEN ‘Once’
ELSE CAST(‘Every ‘
+ RIGHT(scd.freq_subday_interval, 2)
+ ‘ ‘
+ CASE (scd.freq_subday_type)
WHEN 1 THEN ‘Once’
WHEN 4 THEN ‘Minutes’
WHEN 8 THEN ‘Hours’
END AS CHAR(16))
END AS ‘Hourly\Min\Once’
, scd.ActiveEndDate
, scd.ActiveEndTime
, CASE
WHEN o.enabled = 0 THEN ‘Disabled: ‘
ELSE ”
END + o.name AS ‘Operator Notify’
, o.email_address AS ‘Operator Email Address’
FROM msdb.dbo.sysjobs job
LEFT OUTER JOIN (SELECT
js.job_id
, sc.enabled
, sc.schedule_id
, sc.freq_type
, sc.freq_subday_type
, sc.freq_subday_interval
, CASE
WHEN js.next_run_date = 0 THEN sc.active_start_date
ELSE js.next_run_date
END AS ‘NextRunDate’
, CASE
WHEN js.next_run_date = 0 THEN sc.active_start_time
ELSE js.next_run_time
END AS ‘NextRunTime’
, NULLIF(sc.active_end_date, ‘99991231’) AS ‘ActiveEndDate’
, NULLIF(sc.active_end_time, ‘235959’) AS ‘ActiveEndTime’
FROM msdb.dbo.sysjobschedules js
LEFT OUTER JOIN msdb.dbo.sysschedules sc
ON js.schedule_id = sc.schedule_id) scd
ON job.job_id = scd.job_id
LEFT OUTER JOIN (SELECT
job_id
, MAX(jh.run_duration) AS run_duration
FROM msdb.dbo.sysjobhistory jh
GROUP BY job_id) Q1
ON job.job_id = Q1.job_id
LEFT OUTER JOIN msdb.dbo.sysoperators o
ON job.notify_email_operator_id = o.id
This code was written using the Microsoft SQL Server documentation. If you find that this code was copied from else where, please let me know so I can give proper credit.