SQL Job details – The script can be executed across all SQL version from SQL 2000 onwards.
The below are the columns listed in the output
- ServerName
- ScheduleID
- ScheduleName
- IsEnabled
- ScheduleType
- Occurrence
- Recurrence
- Frequency
- ScheduleUsage
- StartDate
- ScheduleUsage
- EndDate
- ScheduleCreatedOn
- ScheduleLastModifiedOn
For more information about the columns refer the below the link
http://technet.microsoft.com/en-us/library/ms178644.aspx
Download :- SQL Jobs Details
Code:-
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 @DML1 VARCHAR(8000)
DECLARE @DML2 VARCHAR(8000)
DECLARE @DML3 VARCHAR(8000)
SET @DML1='SELECT '''+ @@Servername +''' ServerName,
[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 @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]
, [date_created] AS [ScheduleCreatedOn]
, [date_modified] AS [ScheduleLastModifiedOn]
FROM [msdb].[dbo].[sysschedules]
ORDER BY [ScheduleName]
'
SET @DML3=@DML1+@DML2
exec (@DML3)
END
IF (left(cast(SERVERPROPERTY('ProductVersion')as varchar),5)='8.00.' and SERVERPROPERTY('EngineEdition')in(2,3))
BEGIN
SET @DML1='SELECT '''+ @@Servername +''' ServerName,
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
