MSDB - List SQL Agent Jobs

Applicability:

                 SQL Server 2000:        Not Supported**
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    ChillyDBA    
Date:        23 Jul 2012 
                13 Mar 2013 - Added an all-in-one version of the script with all the function calls transformed into in-line SQL.  Better for running on systems where adding functions is not an option.

Description

Retrieves a list of all SQLAgent scheduled jobs, regardless of their type or whether they have been run before. 
The list can be restricted using a single character parameter:

 'Y' --> return only enabled jobs
 'N' --> return only disabled jobs
 'X' --> return only expired jobs (end date is in the past)
 'A' --> return all jobs


It requires the presence of the following functions:

This is a table valued function that can be used standalone, but which is also is consumed by the following stored procedures:


** NOTE:  This code was written for SQL 2005 and greater.  There are slightly different views for obtaining data from SQL 2000, or for obtaining data in SQL 2005+ that has been subjected to an upgrade from SQL 2000.  While upgrading maintenance plans generally works, I have always found it beneficial in the long term to recreate them from scratch.  The feature set is better, and there are some tasks that are not 100% happy working across editions.  I have therefore not included versions of the code that support SQL 2000 version maintenance plans.

Fully standalone code

I have added an extra version of the code in the download section with all the functions transformed back to inline SQL.  No parameters are used. The code can be run on systems where no code changes are allowed/advised and is also easier to execute across multiple servers.

Code

Function:


DROP FUNCTION dbo.udf_GetMSDBJobList
GO

CREATE FUNCTION dbo.udf_GetMSDBJobList
                              
(
                                  
@JobName        SYSNAME,
                                  
@JobTypeFilter  CHAR(1) = 'A'  --defaults to 'All Jobs'
                              
)
                              
RETURNS @MSDBJobList TABLE
  
(
      
Job_ID                  UNIQUEIDENTIFIER,
      
JobName                 SYSNAME,
      
Category                VARCHAR(100),
      
Enabled                 VARCHAR(3),
      
Scheduled               VARCHAR(3),
      
Description             VARCHAR(200),
      
Occurs                  VARCHAR(100),
      
OccursDetail            VARCHAR(100),
      
Frequency               VARCHAR(100),
      
Duration                VARCHAR(100),
      
JobValidUntil           DATETIME

  
)
AS

/***************************************************************
Purpose:   To retrieve details of one or all MSDB jobs
           No execution details are returned, just attributes and schedule information

           @JobTypeFilter Key :
                 'Y' --> return only enabled jobs
                 'N' --> return only disabled jobs
                 'X' --> return only expired jobs (end date is in the past)
                 'A' --> return all jobs  
                
           Utilizes the following functions:
          
               udf_IntToDateString
               udf_IntToTimeString    
               udf_IntToDateTime
               udf_DecodeJobFrequencyInterval
          
Author:        Solihin Ho
History:   18 May 2009 - Initial Issue
           20 Jul 2012 - ChillyDBA - removed the restriction on owner and sysadmin so that
           all jobs are in scope.  Converted to table valued function from an SP so that it
           can easily be consumed by report SPs

****************************************************************/

BEGIN

   INSERT
@MSDBJobList
      
(
          
Job_ID,
          
JobName,
          
Category,
          
Enabled,
          
Scheduled,
          
Description,
          
Occurs,
          
OccursDetail,
          
Frequency,
          
Duration,
          
JobValidUntil
      
)

  
SELECT
      
j.Job_ID                                                            AS Job_ID,
      
j.Name                                                              AS JobName,
      
c.Name                                                              AS Category,
      
      
CASE j.enabled
          
WHEN 1 THEN 'Yes'
          
ELSE 'No'
      
END                                                                 AS Enabled,
      
      
CASE s.enabled
          
WHEN 1 THEN 'Yes'
          
ELSE 'No'
      
END                                                                 AS Scheduled,
      
      
j.Description                                                       AS Description,
      
CASE s.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 SQL Server Agent starts'
          
WHEN 128 THEN 'Start whenever the CPU(s) become idle'
      
END                                                                 AS Occurs,
          
      
CASE s.freq_type
          
WHEN  1 THEN 'O'
          
WHEN  4 THEN 'Every '
                          
+ CONVERT(VARCHAR(5), s.freq_interval)
                           +
' day(s)'
          
WHEN  8 THEN 'Every '
                          
+ CONVERT(VARCHAR(5), s.freq_recurrence_factor)
                           +
' weeks(s) on '
                          
+ dbo.udf_DecodeJobFequencyInterval(s.freq_interval)
                                
          
WHEN 16 THEN 'Day '
                          
+ CONVERT(VARCHAR(5), s.freq_interval)
                           +
' of every '
                          
+ CONVERT(VARCHAR(5), s.freq_recurrence_factor)
                           +
' month(s)'
          
WHEN 32 THEN 'The '
                          
+   CASE s.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 s.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 '
                          
+ CONVERT(VARCHAR(5), s.freq_recurrence_factor)
                           +
' month(s)'
      
END                                                                 AS Occursdetail,
          
      
CASE s.freq_subday_type
          
WHEN 1 THEN 'Occurs once at '
                          
+ dbo.udf_IntToTimeString(s.active_start_time)
          
WHEN 2 THEN 'Occurs every '
                          
+ CONVERT(VARCHAR(5), s.freq_subday_interval)
                           +
' Seconds(s) Starting at '
                          
+ dbo.udf_IntToTimeString(s.active_start_time)
                           +
' ending at '
                          
+ dbo.udf_IntToTimeSttring(s.active_end_time)
          
WHEN 4 THEN 'Occurs every '
                          
+ CONVERT(VARCHAR(5), s.freq_subday_interval)
                           +
' Minute(s) Starting at '
                          
+ dbo.udf_IntToTimeString(s.active_start_time)
                           +
' ending at '
                          
+ dbo.udf_IntToTimeString(s.active_end_time)
          
WHEN 8 THEN 'Occurs every '
                          
+ CONVERT(VARCHAR(5), s.freq_subday_interval)
                           +
' Hour(s) Starting at '
                          
+ dbo.udf_IntToTimeString(s.active_start_time)
                           +
' ending at '
                          
+ dbo.udf_IntToTimeString(s.active_end_time)
      
END                                                                 AS Frequency,
                          
      
CASE
          
WHEN s.freq_type =  1 THEN 'On date: '
                                      
+ dbo.udf_IntToDateString(active_start_date)
                                       +
' At time: '
                                      
+ dbo.udf_IntToTimeString(s.active_start_time)
          
WHEN s.freq_type < 64 THEN 'Start date: '
                                      
+ dbo.udf_IntToDateString(s.active_start_date)
                                       +
' End date: '
                                      
+ dbo.udf_IntToDateString(s.active_end_date)
      
END                                                                 AS Duration,
      
      
dbo.udf_IntToDateTime(s.active_end_date, '000000')                   AS JobValidUntil
      
      
--dbo.udf_IntToDateString(xp.next_run_date)
       -- + ' '
       -- + dbo.udf_IntToTimeString(xp.next_run_time) AS NextRunDate
          
  
FROM  msdb.dbo.sysjobs j (NOLOCK)
  
INNER JOIN msdb.dbo.sysjobschedules js (NOLOCK)
      
ON j.job_id = js.job_id
  
INNER JOIN msdb.dbo.sysschedules s (NOLOCK)
      
ON js.schedule_id = s.schedule_id
  
INNER JOIN msdb.dbo.syscategories c (NOLOCK)
      
ON j.category_id = c.category_id
  
WHERE j.Name = ISNULL(@JobName, j.Name)
  
ORDER BY j.name

  
-- now apply the filters
  
IF @JobTypeFilter <> 'A'
  
BEGIN
       IF
@JobTypeFilter = 'Y'
          
DELETE FROM @MSDBJobList
          
WHERE Enabled = 'No'
      
ELSE IF @JobTypeFilter = 'N'
          
DELETE FROM @MSDBJobList
          
WHERE Enabled = 'Yes'
      
ELSE IF @JobTypeFilter = 'X'
          
DELETE FROM @MSDBJobList
          
WHERE JobValidUntil > CONVERT(VARCHAR(8),GETDATE(),112)
  
END
  
   RETURN
END


Test Code:


SELECT * FROM dbo.udf_GetMSDBJobList(NULL, 'A')
SELECT * FROM dbo.udf_GetMSDBJobList(NULL, 'Y')
SELECT * FROM dbo.udf_GetMSDBJobList(NULL, 'N')
SELECT * FROM dbo.udf_GetMSDBJobList(NULL, 'X')


ċ
GetMSDBJobList.sql
(11k)
Andy Hughes,
Mar 13, 2013, 7:05 AM
ċ
udf_GetMSDBJobList.sql
(6k)
Andy Hughes,
Jul 23, 2012, 10:08 AM
Comments