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

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

About these ads

About Prashanth Jayaram

I’m a Database technologist having 7+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell. hobbies are playing sports and drawing.
This entry was posted in SQL, T-SQL and tagged , , , , . Bookmark the permalink.

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

  1. SQL_DBA says:

    Hello,
    I tried to execute your script as it is but cant. So replaced number of single and double quotes.
    Still getting the below errors. Please provide the exact script which you have executed.
    Thanks

    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘EngineEdition’.
    Msg 102, Level 15, State 1, Line 12
    Incorrect syntax near ‘Yes’.
    Msg 103, Level 15, State 4, Line 116
    The identifier that starts with ‘) AS [ScheduleUsageEndDate]
    , [date_created] AS [ScheduleCreatedOn]
    , [date_modified] AS [ScheduleLastModifiedOn]
    FROM [msdb]‘ is too long. Maximum length is 128.
    Msg 1038, Level 15, State 4, Line 160
    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as “” or [] are not allowed. Change the alias to a valid name.
    Msg 1038, Level 15, State 4, Line 161
    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as “” or [] are not allowed. Change the alias to a valid name.
    Msg 1038, Level 15, State 4, Line 162
    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as “” or [] are not allowed. Change the alias to a valid name.
    Msg 1038, Level 15, State 4, Line 163
    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as “” or [] are not allowed. Change the alias to a valid name.
    Msg 1038, Level 15, State 4, Line 164
    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as “” or [] are not allowed. Change the alias to a valid name.
    Msg 1038, Level 15, State 4, Line 165
    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as “” or [] are not allowed. Change the alias to a valid name.
    Msg 1038, Level 15, State 4, Line 166
    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as “” or [] are not allowed. Change the alias to a valid name.
    Msg 105, Level 15, State 1, Line 245
    Unclosed quotation mark after the character string ‘

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s