DBA_sysjobhistory_config_tbl.sql 1.03 KB
Newer Older
dwentzel's avatar
dwentzel committed
1
IF NOT EXISTS (select * from sys.objects WHERE name = 'DBA_sysjobhistory_config')
dwentzel's avatar
dwentzel committed
2
BEGIN
dwentzel's avatar
dwentzel committed
3
	CREATE TABLE DBA_sysjobhistory_config (
dwentzel's avatar
dwentzel committed
4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
		JobName  varchar(256), --(name of the job for this retention setting)
		CategoryName varchar(256), --(only JobName or CategoryName should be completed)
		NumDaysToRetain int , --(number of days to retain for this entry)
		NumFailureDaysToRetain int --(number of days to retain for failure entries)
	);
END
GO

IF EXISTS (select * from sys.objects WHERE name = 'v_sysjobs')
BEGIN
	DROP VIEW v_sysjobs
END;
GO
CREATE VIEW v_sysjobs
AS 
SELECT 
	CASE c.category_id WHEN 0 THEN NULL ELSE c.name END AS CategoryName,
	j.job_id AS job_id,
	j.name AS JobName
FROM msdb..sysjobs j
JOIN msdb..syscategories c
	ON j.category_id = c.category_id
GO


IF EXISTS (select * from sys.objects WHERE name = 'v_sysjobhistory')
BEGIN
	DROP VIEW v_sysjobhistory
END;
GO
CREATE VIEW v_sysjobhistory
AS 
	SELECT 
		instance_id,
		job_id,
		run_date,
		run_time,
		run_status
	FROM msdb..sysjobhistory
GO