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')