Same SQL statement Mike wrote but adapted to SQL Server 2005 and also displaying subday frequencies for those jobs that run multiples times a day.

/*******************************************************************************

Name:GetJobSchedule(For SQL Server7.0&2000)

Author:M.Pearson

Creation Date:5 Jun 2002

Version:1.0

Program Overview:This queries the sysjobs, sysjobschedules and sysjobhistory table to

produce a resultset showing the jobs on a server plus their schedules

(if applicable) and the maximun duration of the job.

The UNION join is to cater for jobs that have been scheduled but not yet

run, as this information is stored in the 'active_start...' fields of the

sysjobschedules table, whereas if the job has already run the schedule

information is stored in the 'next_run...' fields of the sysjobschedules table.

Modification History:

-------------------------------------------------------------------------------

Version DateNameModification

-------------------------------------------------------------------------------

1.0 5 Jun 2002M.PearsonInital Creation

1.16 May 2009A. GonzalezAdapted to SQL Server 2005 and to show

subday frequencies.

*******************************************************************************/

USE msdb

Go

SELECT dbo.sysjobs.Name AS 'Job Name',

'Job Enabled' = CASE dbo.sysjobs.Enabled

WHEN 1 THEN 'Yes'

WHEN 0 THEN 'No'

END,

'Frequency' = CASE dbo.sysschedules.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,

'Start Date' = CASE active_start_date

WHEN 0 THEN null

ELSE

substring(convert(varchar(15),active_start_date),1,4) + '/' +

substring(convert(varchar(15),active_start_date),5,2) + '/' +

substring(convert(varchar(15),active_start_date),7,2)

END,

'Start Time' = CASE len(active_start_time)

WHEN 1 THEN cast('00:00:0' + right(active_start_time,2) as char(8))

WHEN 2 THEN cast('00:00:' + right(active_start_time,2) as char(8))

WHEN 3 THEN cast('00:0'

+ Left(right(active_start_time,3),1)

+':' + right(active_start_time,2) as char (8))

WHEN 4 THEN cast('00:'

+ Left(right(active_start_time,4),2)

+':' + right(active_start_time,2) as char (8))

WHEN 5 THEN cast('0'

+ Left(right(active_start_time,5),1)

+':' + Left(right(active_start_time,4),2)

+':' + right(active_start_time,2) as char (8))

WHEN 6 THEN cast(Left(right(active_start_time,6),2)

+':' + Left(right(active_start_time,4),2)

+':' + right(active_start_time,2) as char (8))

END,

--active_start_time as 'Start Time',

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 'Max Duration',

CASE(dbo.sysschedules.freq_subday_interval)

WHEN 0 THEN 'Once'

ELSE cast('Every '

+ right(dbo.sysschedules.freq_subday_interval,2)

+ ' '

+ CASE(dbo.sysschedules.freq_subday_type)

WHEN 1 THEN 'Once'

WHEN 4 THEN 'Minutes'

WHEN 8 THEN 'Hours'

END as char(16))

END as 'Subday Frequency'

FROM dbo.sysjobs

LEFT OUTER JOIN dbo.sysjobschedules

ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id

INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id

LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration

FROM dbo.sysjobhistory

GROUP BY job_id) Q1

ON dbo.sysjobs.job_id = Q1.job_id

WHERE Next_run_time = 0

UNION

SELECT dbo.sysjobs.Name AS 'Job Name',

'Job Enabled' = CASE dbo.sysjobs.Enabled

WHEN 1 THEN 'Yes'

WHEN 0 THEN 'No'

END,

'Frequency' = CASE dbo.sysschedules.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,

'Start Date' = CASE next_run_date

WHEN 0 THEN null

ELSE

substring(convert(varchar(15),next_run_date),1,4) + '/' +

substring(convert(varchar(15),next_run_date),5,2) + '/' +

substring(convert(varchar(15),next_run_date),7,2)

END,

'Start Time' = CASE len(next_run_time)

WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))

WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))

WHEN 3 THEN cast('00:0'

+ Left(right(next_run_time,3),1)

+':' + right(next_run_time,2) as char (8))

WHEN 4 THEN cast('00:'

+ Left(right(next_run_time,4),2)

+':' + right(next_run_time,2) as char (8))

WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)

+':' + Left(right(next_run_time,4),2)

+':' + right(next_run_time,2) as char (8))

WHEN 6 THEN cast(Left(right(next_run_time,6),2)

+':' + Left(right(next_run_time,4),2)

+':' + right(next_run_time,2) as char (8))

END,

--next_run_time as 'Start Time',

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 'Max Duration',

CASE(dbo.sysschedules.freq_subday_interval)

WHEN 0 THEN 'Once'

ELSE cast('Every '

+ right(dbo.sysschedules.freq_subday_interval,2)

+ ' '

+ CASE(dbo.sysschedules.freq_subday_type)

WHEN 1 THEN 'Once'

WHEN 4 THEN 'Minutes'

WHEN 8 THEN 'Hours'

END as char(16))

END as 'Subday Frequency'

FROM dbo.sysjobs

LEFT OUTER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id

INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id

LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration

FROM dbo.sysjobhistory

GROUP BY job_id) Q1

ON dbo.sysjobs.job_id = Q1.job_id

WHERE Next_run_time 0

ORDER BY [Start Date],[Start Time]