SQL Server - Jobs overview | Scripting library @ Wetory

Quick overview of all SQL Agent jobs. You can check how many SQL Agent jobs you have created and their basic information. Returns jobs schedule information and status. Something similar happening when you are checking jobs using Management Studio section called Job Activity Monitor. Nothing special happening during its execution even if it looks really long.
Job Activity Monitor window
-- declare variables used 
DECLARE @FILTER VARCHAR(1024) = '%' -- filtering rule there
DECLARE @JOB_ID VARCHAR(200)
DECLARE @SCHED_ID VARCHAR(200)
DECLARE @FREQ_TYPE INT
DECLARE @FREQ_INTERVAL INT
DECLARE @FREQ_SUBDAY_TYPE INT
DECLARE @FREQ_SUBDAY_INTERVAL INT
DECLARE @FREQ_RELATIVE_INTERVAL INT
DECLARE @FREQ_RECURRENCE_FACTOR INT
DECLARE @ACTIVE_START_DATE INT
DECLARE @SCHEDULE VARCHAR(1000)
DECLARE @SCHEDULE_DAY VARCHAR(200)
DECLARE @START_TIME VARCHAR(10)
DECLARE @END_TIME VARCHAR(10)

-- create temporary table for manipulating with data
IF OBJECT_ID('tempdb..#Schedules') IS NOT NULL DROP TABLE #Schedules

CREATE TABLE #Schedules (
	JOB_ID VARCHAR(200)
	,SCHED_ID VARCHAR(200)
	,JOB_NAME SYSNAME
	,[STATUS] INT
	,SCHEDULED INT NULL
	,schedule VARCHAR(1000) NULL
	,FREQ_TYPE INT NULL
	,FREQ_INTERVAL INT NULL
	,FREQ_SUBDAY_TYPE INT NULL
	,FREQ_SUBDAY_INTERVAL INT NULL
	,FREQ_RELATIVE_INTERVAL INT NULL
	,FREQ_RECURRENCE_FACTOR INT NULL
	,ACTIVE_START_DATE INT NULL
	,ACTIVE_END_DATE INT NULL
	,ACTIVE_START_TIME INT NULL
	,ACTIVE_END_TIME INT NULL
	,DATE_CREATED DATETIME NULL
)

-- inert data into temporary table
INSERT INTO #Schedules (
	job_id
	,sched_id
	,job_name
	,[status]
	,scheduled
	,schedule
	,freq_type
	,freq_interval
	,freq_subday_type
	,freq_subday_interval
	,freq_relative_interval
	,freq_recurrence_factor
	,active_start_date
	,active_end_date
	,active_start_time
	,active_end_time
	,date_created
)
SELECT j.job_id
	,sched.schedule_id
	,j.name
	,j.enabled
	,sched.enabled
	,NULL
	,sched.freq_type
	,sched.freq_interval
	,sched.freq_subday_type
	,sched.freq_subday_interval
	,sched.freq_relative_interval
	,sched.freq_recurrence_factor
	,sched.active_start_date
	,sched.active_end_date
	,sched.active_start_time
	,sched.active_end_time
	,j.date_created
FROM sysjobs j
INNER JOIN sysjobschedules s ON j.job_id = s.job_id
INNER JOIN dbo.sysschedules sched ON s.schedule_id = sched.schedule_id
WHERE j.enabled = 1
	AND sched.enabled = 1
	AND j.name LIKE @FILTER

-- iterate through data
WHILE 1 = 1
BEGIN
	SET @SCHEDULE = ''
	IF ( SELECT COUNT(*) FROM #Schedules WHERE scheduled = 1 AND schedule IS NULL ) = 0
		BREAK
	ELSE
	BEGIN
		SELECT @job_id = job_id
			,@sched_id = sched_id
			,@freq_type = freq_type
			,@Freq_Interval = freq_interval
			,@freq_subday_type = freq_subday_type
			,@freq_subday_interval = freq_subday_interval
			,@freq_relative_interval = freq_relative_interval
			,@freq_recurrence_factor = freq_recurrence_factor
			,@active_start_date = active_start_date
			,@start_time = 
			CASE 
				WHEN LEFT(active_start_time, 2) IN (22, 23)
					AND LEN(active_start_time) = 6
					THEN CONVERT(VARCHAR(2), left(active_start_time, 2) - 12) + ':' + SUBSTRING(CAST(active_start_time AS CHAR), 3, 2) + ' P.M'
				WHEN LEFT(active_start_time, 2) = (12)
					AND LEN(active_start_time) = 6
					THEN CAST(LEFT(active_start_time, 2) AS CHAR(2)) + ':' + SUBSTRING(CAST(active_start_time AS CHAR), 3, 2) + ' P.M.'
				WHEN LEFT(active_start_time, 2) BETWEEN 13
						AND 24
					AND LEN(active_start_time) = 6
					THEN CONVERT(VARCHAR(2), left(active_start_time, 2) - 12) + ':' + SUBSTRING(CAST(active_start_time AS CHAR), 3, 2) + ' P.M.'
				WHEN LEFT(active_start_time, 2) IN (10, 11)
					AND LEN(active_start_time) = 6
					THEN CAST(LEFT(active_start_time, 2) AS CHAR(2)) + ':' + SUBSTRING(CAST(active_start_time AS CHAR), 3, 2) + ' A.M.'
				WHEN active_start_time = 0
					THEN '12:00 A.M.'
				WHEN LEN(active_start_time) = 4
					THEN '12:' + CONVERT(VARCHAR(2), left(active_start_time, 2)) + ' A.M.'
				WHEN LEN(active_start_time) = 3
					THEN '12:0' + CONVERT(VARCHAR(2), left(active_start_time, 1)) + ' A.M.'
				WHEN LEN(active_start_time) = 2
					THEN '12:00:' + CONVERT(VARCHAR(2), left(active_start_time, 2)) + ' A.M.'
				WHEN LEN(active_start_time) = 1
					THEN '12:00:0' + CONVERT(VARCHAR(2), left(active_start_time, 2)) + ' A.M.'
				ELSE CAST(LEFT(active_start_time, 1) AS CHAR(1)) + ':' + SUBSTRING(CAST(active_start_time AS CHAR), 2, 2) + ' A.M.'
			END
			,@END_TIME = 
			CASE 
				WHEN LEFT(active_end_time, 2) IN (22, 23)
					AND LEN(active_end_time) = 6
					THEN CONVERT(VARCHAR(2), left(active_end_time, 2) - 12) + ':' + SUBSTRING(CAST(active_end_time AS CHAR), 3, 2) + ' P.M'
				WHEN LEFT(active_end_time, 2) = (12)
					AND LEN(active_end_time) = 6
					THEN CAST(LEFT(active_end_time, 2) AS CHAR(2)) + ':' + SUBSTRING(CAST(active_end_time AS CHAR), 3, 2) + ' P.M.'
				WHEN LEFT(active_end_time, 2) BETWEEN 13
						AND 24
					AND LEN(active_end_time) = 6
					THEN CONVERT(VARCHAR(2), left(active_end_time, 2) - 12) + ':' + SUBSTRING(CAST(active_end_time AS CHAR), 3, 2) + ' P.M.'
				WHEN LEFT(active_end_time, 2) IN (10, 11)
					AND LEN(active_end_time) = 6
					THEN CAST(LEFT(active_end_time, 2) AS CHAR(2)) + ':' + SUBSTRING(CAST(active_end_time AS CHAR), 3, 2) + ' A.M.'
				WHEN active_end_time = 0
					THEN '12:00 A.M.'
				WHEN LEN(active_end_time) = 4
					THEN '12:' + CONVERT(VARCHAR(2), left(active_end_time, 2)) + ' A.M.'
				WHEN LEN(active_end_time) = 3
					THEN '12:0' + CONVERT(VARCHAR(2), left(active_end_time, 1)) + ' A.M.'
				WHEN LEN(active_end_time) = 2
					THEN '12:00:' + CONVERT(VARCHAR(2), left(active_end_time, 2)) + ' A.M.'
				WHEN LEN(active_end_time) = 1
					THEN '12:00:0' + CONVERT(VARCHAR(2), left(active_end_time, 2)) + ' A.M.'
				ELSE CAST(LEFT(active_end_time, 1) AS CHAR(1)) + ':' + SUBSTRING(CAST(active_end_time AS CHAR), 2, 2) + ' A.M.'
			END
		FROM #Schedules
		WHERE schedule IS NULL
			AND scheduled = 1

		IF EXISTS (SELECT @freq_type WHERE @freq_type IN (1, 64))
		BEGIN
			SELECT @SCHEDULE = 
			CASE @freq_type
				WHEN 1
					THEN 'Occurs Once, On ' + cast(@active_start_date AS VARCHAR(8)) + ', At ' + @start_time
				WHEN 64
					THEN 'Occurs When SQL Server Agent Starts'
			END
		END
		ELSE
		BEGIN
			IF @freq_type = 4
			BEGIN
				SELECT @SCHEDULE = 'Occurs Every ' + cast(@freq_interval AS VARCHAR(10)) + ' Day(s)'
			END

			IF @freq_type = 8
			BEGIN
				SELECT @SCHEDULE = 'Occurs Every ' + cast(@freq_recurrence_factor AS VARCHAR(3)) + ' Week(s)'
				SELECT @schedule_day = ''
				IF (SELECT (convert(INT, (@freq_interval / 1 )) % 2)) = 1 SELECT @schedule_day = @schedule_day + 'Sun'
				IF (SELECT (convert(INT, (@freq_interval / 2 )) % 2)) = 1 SELECT @schedule_day = @schedule_day + 'Mon'
				IF (SELECT (convert(INT, (@freq_interval / 4 )) % 2)) = 1 SELECT @schedule_day = @schedule_day + 'Tue'
				IF (SELECT (convert(INT, (@freq_interval / 8 )) % 2)) = 1 SELECT @schedule_day = @schedule_day + 'Wed'
				IF (SELECT (convert(INT, (@freq_interval / 16)) % 2)) = 1 SELECT @schedule_day = @schedule_day + 'Thu'
				IF (SELECT (convert(INT, (@freq_interval / 32)) % 2)) = 1 SELECT @schedule_day = @schedule_day + 'Fri'
				IF (SELECT (convert(INT, (@freq_interval / 64)) % 2)) = 1 SELECT @schedule_day = @schedule_day + 'Sat'
				SELECT @SCHEDULE = @SCHEDULE + ', On ' + @schedule_day
			END
			IF @freq_type = 16
			BEGIN
				SELECT @SCHEDULE = 'Occurs Every ' + cast(@freq_recurrence_factor AS VARCHAR(3)) + ' Month(s) on Day ' + cast(@freq_interval AS VARCHAR(3)) + ' of that Month'
			END

			IF @freq_type = 32
			BEGIN
				SELECT @SCHEDULE = 
				CASE @freq_relative_interval
					WHEN 1 THEN 'First'
					WHEN 2 THEN 'Second'
					WHEN 4 THEN 'Third'
					WHEN 8 THEN 'Fourth'
					WHEN 16 THEN 'Last'
					ELSE 'Not Applicable'
				END

				SELECT @SCHEDULE = 
				CASE @freq_interval
					WHEN 1 THEN 'Occurs Every ' + @SCHEDULE + ' Sunday of the Month'
					WHEN 2 THEN 'Occurs Every ' + @SCHEDULE + ' Monday of the Month'
					WHEN 3 THEN 'Occurs Every ' + @SCHEDULE + ' Tueday of the Month'
					WHEN 4 THEN 'Occurs Every ' + @SCHEDULE + ' Wednesday of the Month'
					WHEN 5 THEN 'Occurs Every ' + @SCHEDULE + ' Thursday of the Month'
					WHEN 6 THEN 'Occurs Every ' + @SCHEDULE + ' Friday of the Month'
					WHEN 7 THEN 'Occurs Every ' + @SCHEDULE + ' Saturday of the Month'
					WHEN 8 THEN 'Occurs Every ' + @SCHEDULE + ' Day of the Month'
					WHEN 9 THEN 'Occurs Every ' + @SCHEDULE + ' Weekday of the Month'
					WHEN 10 THEN 'Occurs Every ' + @SCHEDULE + ' Weekend Day of the Month'
				END
			END

			SELECT @SCHEDULE = 
			CASE @freq_subday_type
				WHEN 1 THEN @SCHEDULE + ', At ' + @start_time
				WHEN 2 THEN @SCHEDULE + ', every ' + cast(@freq_subday_interval AS VARCHAR(3)) + ' Second(s) Between ' + @start_time + ' and ' + @END_TIME
				WHEN 4 THEN @SCHEDULE + ', every ' + cast(@freq_subday_interval AS VARCHAR(3)) + ' Minute(s) Between ' + @start_time + ' and ' + @END_TIME
				WHEN 8 THEN @SCHEDULE + ', every ' + cast(@freq_subday_interval AS VARCHAR(3)) + ' Hour(s) Between ' + @start_time + ' and ' + @END_TIME
			END
		END
	END

	UPDATE #Schedules SET schedule = @SCHEDULE WHERE job_id = @job_id AND sched_id = @sched_Id
END

-- get results from temporary table
SELECT 
	job_name
	,schedule AS 'frequency'
	,[status] = CASE [STATUS]
		WHEN 1 THEN 'ENABLED'
		WHEN 0 THEN 'DISABLED'
		ELSE ' '
	END
	,scheduled = CASE scheduled
		WHEN 1 THEN 'Yes'
		WHEN 0 THEN 'No'
		ELSE ' '
	END
FROM #Schedules
WHERE scheduled = 1
ORDER BY job_name

-- cleanup of temp table
IF OBJECT_ID('tempdb..#SCHEDULES') IS NOT NULL
	DROP TABLE #Schedules
Results

Script results screen:
Script results detailed description:
Column name	Data type	Description
job_name	sysname	Name of SQL Agent job.
frequency	varchar(1000)	Text representation of SQL Agent job schedule.
status	varchar	Can be values ENABLED or DISABLED. Specifying if SQL Agent job is enabled or not.
scheduled	varchar	Can be values Yes or No. Specifying if SQL Agent job is scheduled or not.
Byte converter calculator

Scripting library

SQL Server - Jobs overview

Results

Leave a Reply Cancel reply
