Job Report on SQL 2005
Some times you need acces to jobs' schedule related information in a server, but you do not have acess to the server agent information or there are too many jobs to check them one by one.
We need a helper function "IntegerToBinary" (it is clear what is it for) to extract hiden information in columns like freq_interval from table (view) in the msdb database.
IntegerToBinary is an adaption to T-SQL of the classic algorithm for geting the binary representation of an integer.That function script is in the first section after that is the report's script which display jobs' info.
-- utilitary function to convert Integer values to binary string representation values
CREATE FUNCTION [dbo].[IntegerToBinary]
(
-- Add the parameters for the function here
@PilotintegerNumber INT
)
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @ControlerInt INT
DECLARE @Pilot INT
DECLARE @PilotLenght INT
DECLARE @_strTemp VARCHAR(32)
DECLARE @_result VARCHAR(32)
SET @Pilot=0
SET @ControlerInt=0
SET @PilotLenght=0
SET @_strTemp=''
SET @_result=''
SET @ControlerInt = @PilotintegerNumber;
WHILE @ControlerInt > 0
BEGIN
SET @_strTemp = @_strTemp + CAST((@ControlerInt%2) AS CHAR(1));
SET @ControlerInt = @ControlerInt/2;
END
SET @PilotLenght=LEN(@_strTemp);
WHILE @Pilot < @PilotLenght
BEGIN
SET @_result = @_result + SUBSTRING(@_strTemp,@PilotLenght-@Pilot,1);
SET @Pilot = @Pilot+1;
END
RETURN @_result
END
-- report of active or inactived jobs in a server
-- =============================================
-- Author: Bernabe Diaz
-- =============================================
DECLARE @name NVARCHAR(50),@frq INT,@interv INT
SELECT a.name + ' (' + CAST(@@SERVERNAME AS varchar) + ') ' [name]
,' '
,' '
---- Uncomment this section to see the frequency type
,CASE c.freq_type WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly'
ELSE 'SQL Server Agent start up'
END 'Frequency'
-------------------------------
--,c.freq_interval
-------------------------------
,CASE c.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN (
SUBSTRING( CASE SUBSTRING(REPLICATE('0',7-LEN([dbo].[IntegerToBinary] (c.freq_interval)))+ [dbo].[IntegerToBinary] (c.freq_interval),7,1) WHEN '1' THEN 'Su - 'ELSE '' END +
CASE SUBSTRING(REPLICATE('0',7-LEN([dbo].[IntegerToBinary] (c.freq_interval)))+ [dbo].[IntegerToBinary] (c.freq_interval),6,1) WHEN '1' THEN 'M - 'ELSE '' END +
CASE SUBSTRING(REPLICATE('0',7-LEN([dbo].[IntegerToBinary] (c.freq_interval)))+ [dbo].[IntegerToBinary] (c.freq_interval),5,1) WHEN '1' THEN 'Tu - 'ELSE '' END +
CASE SUBSTRING(REPLICATE('0',7-LEN([dbo].[IntegerToBinary] (c.freq_interval)))+ [dbo].[IntegerToBinary] (c.freq_interval),4,1) WHEN '1' THEN 'W - 'ELSE '' END +
CASE SUBSTRING(REPLICATE('0',7-LEN([dbo].[IntegerToBinary] (c.freq_interval)))+ [dbo].[IntegerToBinary] (c.freq_interval),3,1) WHEN '1' THEN 'Th - 'ELSE '' END +
CASE SUBSTRING(REPLICATE('0',7-LEN([dbo].[IntegerToBinary] (c.freq_interval)))+ [dbo].[IntegerToBinary] (c.freq_interval),2,1) WHEN '1' THEN 'F - 'ELSE '' END +
CASE SUBSTRING(REPLICATE('0',7-LEN([dbo].[IntegerToBinary] (c.freq_interval)))+ [dbo].[IntegerToBinary] (c.freq_interval),1,1) WHEN '1' THEN 'Sa - 'ELSE '' END
,1,
LEN(
CASE SUBSTRING(REPLICATE('0',7-LEN([dbo].[IntegerToBinary] (c.freq_interval)))+ [dbo].[IntegerToBinary] (c.freq_interval),7,1) WHEN '1' THEN 'Su - 'ELSE '' END +
CASE SUBSTRING(REPLICATE('0',7-LEN([dbo].[IntegerToBinary] (c.freq_interval)))+ [dbo].[IntegerToBinary] (c.freq_interval),6,1) WHEN '1' THEN 'M - 'ELSE '' END +
CASE SUBSTRING(REPLICATE('0',7-LEN([dbo].[IntegerToBinary] (c.freq_interval)))+ [dbo].[IntegerToBinary] (c.freq_interval),5,1) WHEN '1' THEN 'Tu - 'ELSE '' END +
CASE SUBSTRING(REPLICATE('0',7-LEN([dbo].[IntegerToBinary] (c.freq_interval)))+ [dbo].[IntegerToBinary] (c.freq_interval),4,1) WHEN '1' THEN 'W - 'ELSE '' END +
CASE SUBSTRING(REPLICATE('0',7-LEN([dbo].[IntegerToBinary] (c.freq_interval)))+ [dbo].[IntegerToBinary] (c.freq_interval),3,1) WHEN '1' THEN 'Th - 'ELSE '' END +
CASE SUBSTRING(REPLICATE('0',7-LEN([dbo].[IntegerToBinary] (c.freq_interval)))+ [dbo].[IntegerToBinary] (c.freq_interval),2,1) WHEN '1' THEN 'F - 'ELSE '' END +
CASE SUBSTRING(REPLICATE('0',7-LEN([dbo].[IntegerToBinary] (c.freq_interval)))+ [dbo].[IntegerToBinary] (c.freq_interval),1,1) WHEN '1' THEN 'Sa - 'ELSE '' END
) -2)
)
WHEN 16 THEN
( 'Monthly :' +
CASE c.freq_interval WHEN 1 THEN 'Su - ' ELSE '' END +
CASE c.freq_interval WHEN 2 THEN 'M - ' ELSE '' END +
CASE c.freq_interval WHEN 3 THEN 'Tu - ' ELSE '' END +
CASE c.freq_interval WHEN 4 THEN 'W - ' ELSE '' END +
CASE c.freq_interval WHEN 5 THEN 'Th - ' ELSE '' END +
CASE c.freq_interval WHEN 6 THEN 'F - ' ELSE '' END +
CASE c.freq_interval WHEN 7 THEN 'Sa - ' ELSE '' END +
CASE c.freq_interval WHEN 8 THEN '[Day] - ' ELSE '' END +
CASE c.freq_interval WHEN 9 THEN 'Week day - ' ELSE '' END +
CASE c.freq_interval WHEN 10 THEN 'Weekend day - ' ELSE '' END
)
WHEN 32 THEN (
SUBSTRING(
CASE c.freq_interval WHEN 1 THEN (
CASE c.freq_relative_interval WHEN 1 THEN 'First' ELSE '' END +
CASE c.freq_relative_interval WHEN 2 THEN 'Second' ELSE '' END +
CASE c.freq_relative_interval WHEN 4 THEN 'Third' ELSE '' END +
CASE c.freq_relative_interval WHEN 8 THEN 'Fourth' ELSE '' END +
CASE c.freq_relative_interval WHEN 16 THEN 'Last' ELSE '' END
)+' Su - '
ELSE '' END +
CASE c.freq_interval WHEN 2 THEN (
CASE c.freq_relative_interval WHEN 1 THEN 'First' ELSE '' END +
CASE c.freq_relative_interval WHEN 2 THEN 'Second' ELSE '' END +
CASE c.freq_relative_interval WHEN 4 THEN 'Third' ELSE '' END +
CASE c.freq_relative_interval WHEN 8 THEN 'Fourth' ELSE '' END +
CASE c.freq_relative_interval WHEN 16 THEN 'Last' ELSE '' END
)+' M - ' ELSE '' END +
CASE c.freq_interval WHEN 3 THEN (
CASE c.freq_relative_interval WHEN 1 THEN 'First' ELSE '' END +
CASE c.freq_relative_interval WHEN 2 THEN 'Second' ELSE '' END +
CASE c.freq_relative_interval WHEN 4 THEN 'Third' ELSE '' END +
CASE c.freq_relative_interval WHEN 8 THEN 'Fourth' ELSE '' END +
CASE c.freq_relative_interval WHEN 16 THEN 'Last' ELSE '' END
)+' Tu - ' ELSE '' END +
CASE c.freq_interval WHEN 4 THEN (
CASE c.freq_relative_interval WHEN 1 THEN 'First' ELSE '' END +
CASE c.freq_relative_interval WHEN 2 THEN 'Second' ELSE '' END +
CASE c.freq_relative_interval WHEN 4 THEN 'Third' ELSE '' END +
CASE c.freq_relative_interval WHEN 8 THEN 'Fourth' ELSE '' END +
CASE c.freq_relative_interval WHEN 16 THEN 'Last' ELSE '' END
)+' W - ' ELSE '' END +
CASE c.freq_interval WHEN 5 THEN (
CASE c.freq_relative_interval WHEN 1 THEN 'First' ELSE '' END +
CASE c.freq_relative_interval WHEN 2 THEN 'Second' ELSE '' END +
CASE c.freq_relative_interval WHEN 4 THEN 'Third' ELSE '' END +
CASE c.freq_relative_interval WHEN 8 THEN 'Fourth' ELSE '' END +
CASE c.freq_relative_interval WHEN 16 THEN 'Last' ELSE '' END
)+' Th - ' ELSE '' END +
CASE c.freq_interval WHEN 6 THEN (
CASE c.freq_relative_interval WHEN 1 THEN 'First' ELSE '' END +
CASE c.freq_relative_interval WHEN 2 THEN 'Second' ELSE '' END +
CASE c.freq_relative_interval WHEN 4 THEN 'Third' ELSE '' END +
CASE c.freq_relative_interval WHEN 8 THEN 'Fourth' ELSE '' END +
CASE c.freq_relative_interval WHEN 16 THEN 'Last' ELSE '' END
)+' F - ' ELSE '' END +
CASE c.freq_interval WHEN 7 THEN (
CASE c.freq_relative_interval WHEN 1 THEN 'First' ELSE '' END +
CASE c.freq_relative_interval WHEN 2 THEN 'Second' ELSE '' END +
CASE c.freq_relative_interval WHEN 4 THEN 'Third' ELSE '' END +
CASE c.freq_relative_interval WHEN 8 THEN 'Fourth' ELSE '' END +
CASE c.freq_relative_interval WHEN 16 THEN 'Last' ELSE '' END
)+' Sa - ' ELSE '' END +
CASE c.freq_interval WHEN 8 THEN (
CASE c.freq_relative_interval WHEN 1 THEN 'First' ELSE '' END +
CASE c.freq_relative_interval WHEN 2 THEN 'Second' ELSE '' END +
CASE c.freq_relative_interval WHEN 4 THEN 'Third' ELSE '' END +
CASE c.freq_relative_interval WHEN 8 THEN 'Fourth' ELSE '' END +
CASE c.freq_relative_interval WHEN 16 THEN 'Last' ELSE '' END
)+' [Day] - ' ELSE '' END +
CASE c.freq_interval WHEN 9 THEN (
CASE c.freq_relative_interval WHEN 1 THEN 'First' ELSE '' END +
CASE c.freq_relative_interval WHEN 2 THEN 'Second' ELSE '' END +
CASE c.freq_relative_interval WHEN 4 THEN 'Third' ELSE '' END +
CASE c.freq_relative_interval WHEN 8 THEN 'Fourth' ELSE '' END +
CASE c.freq_relative_interval WHEN 16 THEN 'Last' ELSE '' END
)+' Week day - ' ELSE '' END +
CASE c.freq_interval WHEN 10 THEN (
CASE c.freq_relative_interval WHEN 1 THEN 'First' ELSE '' END +
CASE c.freq_relative_interval WHEN 2 THEN 'Second' ELSE '' END +
CASE c.freq_relative_interval WHEN 4 THEN 'Third' ELSE '' END +
CASE c.freq_relative_interval WHEN 8 THEN 'Fourth' ELSE '' END +
CASE c.freq_relative_interval WHEN 16 THEN 'Last' ELSE '' END
)+' Weekend day - ' ELSE '' END
+ ' of the month ',1,
LEN(
CASE c.freq_interval WHEN 1 THEN (
CASE c.freq_relative_interval WHEN 1 THEN 'First' ELSE '' END +
CASE c.freq_relative_interval WHEN 2 THEN 'Second' ELSE '' END +
CASE c.freq_relative_interval WHEN 4 THEN 'Third' ELSE '' END +
CASE c.freq_relative_interval WHEN 8 THEN 'Fourth' ELSE '' END +
CASE c.freq_relative_interval WHEN 16 THEN 'Last' ELSE '' END
)+' Su - '
ELSE '' END +
CASE c.freq_interval WHEN 2 THEN (
CASE c.freq_relative_interval WHEN 1 THEN 'First' ELSE '' END +
CASE c.freq_relative_interval WHEN 2 THEN 'Second' ELSE '' END +
CASE c.freq_relative_interval WHEN 4 THEN 'Third' ELSE '' END +
CASE c.freq_relative_interval WHEN 8 THEN 'Fourth' ELSE '' END +
CASE c.freq_relative_interval WHEN 16 THEN 'Last' ELSE '' END
)+' M - ' ELSE '' END +
CASE c.freq_interval WHEN 3 THEN (
CASE c.freq_relative_interval WHEN 1 THEN 'First' ELSE '' END +
CASE c.freq_relative_interval WHEN 2 THEN 'Second' ELSE '' END +
CASE c.freq_relative_interval WHEN 4 THEN 'Third' ELSE '' END +
CASE c.freq_relative_interval WHEN 8 THEN 'Fourth' ELSE '' END +
CASE c.freq_relative_interval WHEN 16 THEN 'Last' ELSE '' END
)+' Tu - ' ELSE '' END +
CASE c.freq_interval WHEN 4 THEN (
CASE c.freq_relative_interval WHEN 1 THEN 'First' ELSE '' END +
CASE c.freq_relative_interval WHEN 2 THEN 'Second' ELSE '' END +
CASE c.freq_relative_interval WHEN 4 THEN 'Third' ELSE '' END +
CASE c.freq_relative_interval WHEN 8 THEN 'Fourth' ELSE '' END +
CASE c.freq_relative_interval WHEN 16 THEN 'Last' ELSE '' END
)+' W - ' ELSE '' END +
CASE c.freq_interval WHEN 5 THEN (
CASE c.freq_relative_interval WHEN 1 THEN 'First' ELSE '' END +
CASE c.freq_relative_interval WHEN 2 THEN 'Second' ELSE '' END +
CASE c.freq_relative_interval WHEN 4 THEN 'Third' ELSE '' END +
CASE c.freq_relative_interval WHEN 8 THEN 'Fourth' ELSE '' END +
CASE c.freq_relative_interval WHEN 16 THEN 'Last' ELSE '' END
)+' Th - ' ELSE '' END +
CASE c.freq_interval WHEN 6 THEN (
CASE c.freq_relative_interval WHEN 1 THEN 'First' ELSE '' END +
CASE c.freq_relative_interval WHEN 2 THEN 'Second' ELSE '' END +
CASE c.freq_relative_interval WHEN 4 THEN 'Third' ELSE '' END +
CASE c.freq_relative_interval WHEN 8 THEN 'Fourth' ELSE '' END +
CASE c.freq_relative_interval WHEN 16 THEN 'Last' ELSE '' END
)+' F - ' + ' of the month -'ELSE '' END +
CASE c.freq_interval WHEN 7 THEN (
CASE c.freq_relative_interval WHEN 1 THEN 'First' ELSE '' END +
CASE c.freq_relative_interval WHEN 2 THEN 'Second' ELSE '' END +
CASE c.freq_relative_interval WHEN 4 THEN 'Third' ELSE '' END +
CASE c.freq_relative_interval WHEN 8 THEN 'Fourth' ELSE '' END +
CASE c.freq_relative_interval WHEN 16 THEN 'Last' ELSE '' END
)+' Sa - ' ELSE '' END +
CASE c.freq_interval WHEN 8 THEN (
CASE c.freq_relative_interval WHEN 1 THEN 'First' ELSE '' END +
CASE c.freq_relative_interval WHEN 2 THEN 'Second' ELSE '' END +
CASE c.freq_relative_interval WHEN 4 THEN 'Third' ELSE '' END +
CASE c.freq_relative_interval WHEN 8 THEN 'Fourth' ELSE '' END +
CASE c.freq_relative_interval WHEN 16 THEN 'Last' ELSE '' END
)+' [Day] - ' ELSE '' END +
CASE c.freq_interval WHEN 9 THEN (
CASE c.freq_relative_interval WHEN 1 THEN 'First' ELSE '' END +
CASE c.freq_relative_interval WHEN 2 THEN 'Second' ELSE '' END +
CASE c.freq_relative_interval WHEN 4 THEN 'Third' ELSE '' END +
CASE c.freq_relative_interval WHEN 8 THEN 'Fourth' ELSE '' END +
CASE c.freq_relative_interval WHEN 16 THEN 'Last' ELSE '' END
)+' Week day - ' ELSE '' END +
CASE c.freq_interval WHEN 10 THEN (
CASE c.freq_relative_interval WHEN 1 THEN 'First' ELSE '' END +
CASE c.freq_relative_interval WHEN 2 THEN 'Second' ELSE '' END +
CASE c.freq_relative_interval WHEN 4 THEN 'Third' ELSE '' END +
CASE c.freq_relative_interval WHEN 8 THEN 'Fourth' ELSE '' END +
CASE c.freq_relative_interval WHEN 16 THEN 'Last' ELSE '' END
)+' Weekend day - ' ELSE '' END
+' of the month -') -2)
)
END INTERVAL
-------------------------------
,CAST(c.active_start_time AS CHAR(6)) + ' - '+
CAST(c.active_end_time AS CHAR(6)) 'Time window'
,a.description
,CASE a.enabled WHEN 1 THEN 'Active' ELSE 'Deactivated' END enable
FROM msdb..sysschedules c
INNER JOIN msdb..sysjobschedules b
ON c.schedule_id=b.schedule_id
INNER JOIN msdb..sysjobs a
ON a.job_id = b.job_id
-- AND (a.name LIKE '%'+LTRIM(RTRIM( '<<name filter1>>'))+'%'
-- OR
-- a.name LIKE '%'+LTRIM(RTRIM( '<<name filter2>>'))+'%'
-- .
-- .
-- . and so on
-- )
ORDER BY a.name 
