SQL Jobs – Complete Information – SQL 2000/2005/2008/R2

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

  1. ServerName
  2. ScheduleID
  3. ScheduleName
  4. IsEnabled
  5. ScheduleType
  6. Occurrence
  7. Recurrence
  8. Frequency
  9. ScheduleUsage
  10. StartDate
  11. ScheduleUsage
  12. EndDate
  13. ScheduleCreatedOn
  14. 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
Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in SQL and tagged , , , . Bookmark the permalink.

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