http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/2013/08/12/sql-one-query-to-fetch-sql-jobs-details-of-all-sql-version-20000508r2/

Printed 2014/04/20 03:48PM

SQL – One Query to fetch SQL Jobs Details of All SQL Version – 2000/05/08/R2

By Prashanth Jayaram, 2013/08/12

Single Query to fetch complete details of sql jobs and you can run it across different version of SQL [2000/2005/2008 and R2]. I’ve undergone dynamic query string limitation issue and hence I’m including the workaround details in the same post. The dynamic query cannot exceed 4000 characters. The workaround is to split your one big string into multiple smaller strings and execute them by using concatenation operator(“+”)

For Example -

DECLARE @DML1 NVARCHAR(4000)
DECLARE @DML2 NVARCHAR(4000)

SET @DML1 = ‘select *’
SET @DML2 = ‘FROM msdb.dbo.sysjobschedules’

–Now execute them together
EXEC (@DML1 + @DML2)

*****************************************************************************

Query to Pull SQL Jobs Details – Copy and Paste the below code in SSMS 

********************************************************************************

IF (left(cast(SERVERPROPERTY(‘ProductVersion’)as varchar),5)=’10.00′ and SERVERPROPERTY(‘EngineEdition’)=3) OR (left(cast(SERVERPROPERTY(‘ProductVersion’)as varchar),5)=’10.50′ and SERVERPROPERTY(‘EngineEdition’)in(2,3)) OR (left(cast(SERVERPROPERTY(‘ProductVersion’)as varchar),5)=’9.00.’ and SERVERPROPERTY(‘EngineEdition’)in(2,3))
BEGIN
DECLARE @DML11 VARCHAR(8000)
DECLARE @DML12 VARCHAR(8000)
DECLARE @DML13 VARCHAR(8000)

SET @DML11=’SELECT ”’+ @@Servername +”’ ServerName,@@version Version
[Schedule_UID] AS [ScheduleID]
, [name] AS [ScheduleName]
, CASE [enabled]
WHEN 1 THEN ”Yes”
WHEN 0 THEN ”No”
END AS [IsEnabled]
, CASE
WHEN [freq_type] = 64 THEN ”Start automatically when SQL Server Agent starts”
WHEN [freq_type] = 128 THEN ”Start whenever the CPUs become idle”
WHEN [freq_type] IN (4,8,16,32) THEN ”Recurring”
WHEN [freq_type] = 1 THEN ”One Time”
END [ScheduleType]
, CASE [freq_type]
WHEN 1 THEN ”One Time”
WHEN 4 THEN ”Daily”
WHEN 8 THEN ”Weekly”
WHEN 16 THEN ”Monthly”
WHEN 32 THEN ”Monthly – Relative to Frequency Interval”
WHEN 64 THEN ”Start automatically when SQL Server Agent starts”
WHEN 128 THEN ”Start whenever the CPUs become idle”
END [Occurrence]
,
CASE [freq_type]
WHEN 4 THEN ”Occurs every ” + CAST([freq_interval] AS VARCHAR(3)) + ” day(s)”
WHEN 8 THEN ”Occurs every ” + CAST([freq_recurrence_factor] AS VARCHAR(3))
+ ” week(s) on ”
+ CASE WHEN [freq_interval] & 1 = 1 THEN ”Sunday” ELSE ”” END
+ CASE WHEN [freq_interval] & 2 = 2 THEN ”, Monday” ELSE ”” END
+ CASE WHEN [freq_interval] & 4 = 4 THEN ”, Tuesday” ELSE ”” END
+ CASE WHEN [freq_interval] & 8 = 8 THEN ”, Wednesday” ELSE ”” END
+ CASE WHEN [freq_interval] & 16 = 16 THEN ”, Thursday” ELSE ”” END
+ CASE WHEN [freq_interval] & 32 = 32 THEN ”, Friday” ELSE ”” END
+ CASE WHEN [freq_interval] & 64 = 64 THEN ”, Saturday” ELSE ”” END
WHEN 16 THEN ”Occurs on Day ” + CAST([freq_interval] AS VARCHAR(3))
+ ” of every ”
+ CAST([freq_recurrence_factor] AS VARCHAR(3)) + ” month(s)”
WHEN 32 THEN ”Occurs on ”
+ CASE [freq_relative_interval]
WHEN 1 THEN ”First”
WHEN 2 THEN ”Second”
WHEN 4 THEN ”Third”
WHEN 8 THEN ”Fourth”
WHEN 16 THEN ”Last”
END
+ ” ”
+ CASE [freq_interval]
WHEN 1 THEN ”Sunday”
WHEN 2 THEN ”Monday”
WHEN 3 THEN ”Tuesday”
WHEN 4 THEN ”Wednesday”
WHEN 5 THEN ”Thursday”
WHEN 6 THEN ”Friday”
WHEN 7 THEN ”Saturday”
WHEN 8 THEN ”Day”
WHEN 9 THEN ”Weekday”
WHEN 10 THEN ”Weekend day”
END
+ ” of every ” + CAST([freq_recurrence_factor] AS VARCHAR(3))
+ ” month(s)”
END AS [Recurrence]
,

CASE [freq_subday_type]
WHEN 1 THEN ”Occurs once at ”
+ STUFF(
STUFF(RIGHT(”000000” + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ”:”)
, 6, 0, ”:”)
WHEN 2 THEN ”Occurs every ”
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ” Second(s) between ”
+ STUFF(
STUFF(RIGHT(”000000” + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ”:”)
, 6, 0, ”:”)
+ ” & ”
+ STUFF(
STUFF(RIGHT(”000000” + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ”:”)
, 6, 0, ”:”)’
SET @DML12=’WHEN 4 THEN ”Occurs every ”
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ” Minute(s) between ”
+ STUFF(
STUFF(RIGHT(”000000” + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ”:”)
, 6, 0, ”:”)
+ ” & ”
+ STUFF(
STUFF(RIGHT(”000000” + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ”:”)
, 6, 0, ”:”)
WHEN 8 THEN ”Occurs every ”
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ” Hour(s) between ”
+ STUFF(
STUFF(RIGHT(”000000” + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ”:”)
, 6, 0, ”:”)
+ ” & ”
+ STUFF(
STUFF(RIGHT(”000000” + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ”:”)
, 6, 0, ”:”)
END [Frequency]
, STUFF(
STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, ”-”)
, 8, 0, ”-”) AS [ScheduleUsageStartDate]
, STUFF(
STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, ”-”)
, 8, 0, ”-”) AS [ScheduleUsageEndDate]
, [date_created] AS [ScheduleCreatedOn]
, [date_modified] AS [ScheduleLastModifiedOn]
FROM [msdb].[dbo].[sysschedules]
ORDER BY [ScheduleName]

SET @DML13=@DML11+@DML12
exec (@DML13)

END
IF (left(cast(SERVERPROPERTY(‘ProductVersion’)as varchar),5)=’8.00.’ and SERVERPROPERTY(‘EngineEdition’)=3)
BEGIN
DECLARE @DML1 VARCHAR(8000)
DECLARE @DML2 VARCHAR(8000)
DECLARE @DML3 VARCHAR(8000)

SET @DML1=’SELECT ”’+ @@Servername +”’ ServerName,@@version Version,
so.[job_id] AS [ScheduleID]
, so.[name] AS [ScheduleName]
, CASE so.[enabled]
WHEN 1 THEN ”Yes”
WHEN 0 THEN ”No”
END AS [IsEnabled]
, CASE
WHEN [freq_type] = 64 THEN ”Start automatically when SQL Server Agent starts”
WHEN [freq_type] = 128 THEN ”Start whenever the CPUs become idle”
WHEN [freq_type] IN (4,8,16,32) THEN ”Recurring”
WHEN [freq_type] = 1 THEN ”One Time”
END [ScheduleType]
, CASE [freq_type]
WHEN 1 THEN ”One Time”
WHEN 4 THEN ”Daily”
WHEN 8 THEN ”Weekly”
WHEN 16 THEN ”Monthly”
WHEN 32 THEN ”Monthly – Relative to Frequency Interval”
WHEN 64 THEN ”Start automatically when SQL Server Agent starts”
WHEN 128 THEN ”Start whenever the CPUs become idle”
END [Occurrence]
,
CASE [freq_type]
WHEN 4 THEN ”Occurs every ” + CAST([freq_interval] AS VARCHAR(3)) + ” day(s)”
WHEN 8 THEN ”Occurs every ” + CAST([freq_recurrence_factor] AS VARCHAR(3))
+ ” week(s) on ”
+ CASE WHEN [freq_interval] & 1 = 1 THEN ”Sunday” ELSE ”” END
+ CASE WHEN [freq_interval] & 2 = 2 THEN ”, Monday” ELSE ”” END
+ CASE WHEN [freq_interval] & 4 = 4 THEN ”, Tuesday” ELSE ”” END
+ CASE WHEN [freq_interval] & 8 = 8 THEN ”, Wednesday” ELSE ”” END
+ CASE WHEN [freq_interval] & 16 = 16 THEN ”, Thursday” ELSE ”” END
+ CASE WHEN [freq_interval] & 32 = 32 THEN ”, Friday” ELSE ”” END
+ CASE WHEN [freq_interval] & 64 = 64 THEN ”, Saturday” ELSE ”” END
WHEN 16 THEN ”Occurs on Day ” + CAST([freq_interval] AS VARCHAR(3))
+ ” of every ”
+ CAST([freq_recurrence_factor] AS VARCHAR(3)) + ” month(s)”
WHEN 32 THEN ”Occurs on ”
+ CASE [freq_relative_interval]
WHEN 1 THEN ”First”
WHEN 2 THEN ”Second”
WHEN 4 THEN ”Third”
WHEN 8 THEN ”Fourth”
WHEN 16 THEN ”Last”
END
+ ” ”
+ CASE [freq_interval]
WHEN 1 THEN ”Sunday”
WHEN 2 THEN ”Monday”
WHEN 3 THEN ”Tuesday”
WHEN 4 THEN ”Wednesday”
WHEN 5 THEN ”Thursday”
WHEN 6 THEN ”Friday”
WHEN 7 THEN ”Saturday”
WHEN 8 THEN ”Day”
WHEN 9 THEN ”Weekday”
WHEN 10 THEN ”Weekend day”
END
+ ” of every ” + CAST([freq_recurrence_factor] AS VARCHAR(3))
+ ” month(s)”
END AS [Recurrence]
,

CASE [freq_subday_type]
WHEN 1 THEN ”Occurs once at ”
+ STUFF(
STUFF(RIGHT(”000000” + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ”:”)
, 6, 0, ”:”)
WHEN 2 THEN ”Occurs every ”
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ” Second(s) between ”
+ STUFF(
STUFF(RIGHT(”000000” + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ”:”)
, 6, 0, ”:”)
+ ” & ”
+ STUFF(
STUFF(RIGHT(”000000” + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ”:”)
, 6, 0, ”:”)’
SET @DML2=’WHEN 4 THEN ”Occurs every ”
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ” Minute(s) between ”
+ STUFF(
STUFF(RIGHT(”000000” + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ”:”)
, 6, 0, ”:”)
+ ” & ”
+ STUFF(
STUFF(RIGHT(”000000” + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ”:”)
, 6, 0, ”:”)
WHEN 8 THEN ”Occurs every ”
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ” Hour(s) between ”
+ STUFF(
STUFF(RIGHT(”000000” + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ”:”)
, 6, 0, ”:”)
+ ” & ”
+ STUFF(
STUFF(RIGHT(”000000” + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ”:”)
, 6, 0, ”:”)
END [Frequency]
, STUFF(
STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, ”-”)
, 8, 0, ”-”) AS [ScheduleUsageStartDate]
, STUFF(
STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, ”-”)
, 8, 0, ”-”) AS [ScheduleUsageEndDate]
, so.[date_created] AS [ScheduleCreatedOn]
, [date_modified] AS [ScheduleLastModifiedOn]
FROM [msdb].[dbo].[sysjobschedules] sj
inner join msdb.dbo.sysjobs so on so.job_id=sj.job_id’

SET @DML3=@DML1+@DML2
exec (@DML3)
END

********************************************************************************

Download the code here –  SQLJob

Output- 

SQLJobsDetails



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.