InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 09:29:17 GMT20http://www.sqlservercentral.com/Forums/Topic410557-116-1.aspxSame SQL statement Mike wrote but adapted to SQL Server 2005 and also displaying subday frequencies for those jobs that run multiples times a day.[code]/*******************************************************************************Name: GetJobSchedule (For SQL Server7.0&2000)Author: M.PearsonCreation Date: 5 Jun 2002Version: 1.0Program 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 Date Name Modification-------------------------------------------------------------------------------1.0 5 Jun 2002 M.Pearson Inital Creation1.1 6 May 2009 A. Gonzalez Adapted to SQL Server 2005 and to show subday frequencies.*******************************************************************************/USE msdbGoSELECT 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_idINNER 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) Q1ON dbo.sysjobs.job_id = Q1.job_idWHERE Next_run_time = 0UNIONSELECT 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_idINNER 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) Q1ON dbo.sysjobs.job_id = Q1.job_idWHERE Next_run_time <> 0ORDER BY [Start Date],[Start Time][/code]Wed, 06 May 2009 08:49:05 GMTagonzal2http://www.sqlservercentral.com/Forums/Topic410557-116-1.aspxDo you have the same for SQL2005? Tue, 04 Dec 2007 13:43:11 GMTSergey Kazachenkohttp://www.sqlservercentral.com/Forums/Topic410557-116-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Maintenance+and+Management/30381/">List All Jobs and Their Schedules</A>[/B]Sun, 14 Oct 2007 00:03:08 GMTMike Pearson