Quick and Dirty SQL Agent Monitoring Data Mart

/*

SQL AGENT DATA MART: CREATE_SQL_OBJECTS.sql

*/

USE dba

GO

SELECT @@SERVERNAME, DB_NAME()

GO

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE [name] = 'sqlagent')

BEGIN

EXEC sp_executesql @stmt = N'CREATE SCHEMA sqlagent';

END

GO

IF NOT EXISTS (

SELECT 1 

FROM INFORMATION_SCHEMA.TABLES 

WHERE [TABLE_SCHEMA] = 'sqlagent'

AND [TABLE_NAME] = 'STAGE_JobsRunning'

)

BEGIN

CREATE TABLE [sqlagent].[STAGE_JobsRunning] (

[RefreshKey] INT,

[job_id] uniqueidentifier,

[job_name] nvarchar(128),

[start_execution_date] datetime,

[last_executed_step_id] int,

[last_executed_step_date] datetime

)

END

GO

IF NOT EXISTS (

SELECT 1

FROM sys.sequences

WHERE [name] = 'RefreshKey'

AND [schema_id] = (SELECT [schema_id] FROM sys.schemas WHERE [name] = 'sqlagent')

)

BEGIN

CREATE SEQUENCE [sqlagent].[RefreshKey]

START WITH 1  

INCREMENT BY 1;  

END

GO