گزارش جاب های SqlServer
گاهی اوقات ممکن است نیاز داشته باشید به جزئیات مربوط به زمان‌بندی جاب ها (Jobs) در یک سرور دسترسی پیدا کنید، اما یا دسترسی مستقیم به “SQL Server Agent” ندارید، یا تعداد زیادی Job وجود دارد که بررسی تک تک آن‌ها به صورت دستی کار دشواری است.
برای این منظور، به تابع کمکی‌ای به نام “IntegerToBinary” نیاز داریم (که وظیفه آن واضح است) تا بتوانیم اطلاعات رمزگذاری شده موجود در ستون‌هایی مانند freq_interval از جدول مربوط به پایگاه داده‌ی msdb را استخراج کنیم.
تابع “IntegerToBinary” نسخه‌ای تطبیق یافته از الگوریتم کلاسیک تبدیل یک عدد صحیح به نمایش دودویی (باینری) در T-SQL است. ابتدا اسکریپت این تابع ارائه می‌شود و سپس اسکریپت گزارش‌دهی که اطلاعات Jobs را نمایش می‌دهد در ادامه می‌آید.
-- 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
-- =============================================
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
