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