Change Monitor in SQL Server 2005
I work in an environment where too many people have too many permissions across all of our servers from development to production. Since we do not typically go out and purchase outside software, it was my pleasure to return once again and mine SQL Server system views for the information I wanted.
I quickly found that the sys.objects table alone was not doing the trick since the modified date can update for various reasons, such as a dropped index. We often drop indices before loading data and recreate them after the load is finished. Which leads to another problem: the newly created index has a newly created object_id to go with it. Who needs to deal with ever changing object IDs? The solution is to track the true primary keys of objects and ignore object_id altogether.
The top-most level, "object", I defined thus:
server_name varchar(128) not null
,database_name varchar(128) not null
,[schema_name] varchar(50) not null
,[object_name] varchar(128) not null
,type_desc varchar(60) not null
Some column definitions were modified for the sake of my own indexes, because of the 900 byte restriction.
For object definition, we get the colid and text columns from sys.syscomments, and the primary key of the "object definition" table is expanded to include colid.
For table schema, we get the ordinal_position from both information_schema.columns (tables and views) & information_schema.routine_columns (table valued functions). So, the primary key of the "table schema" table is expanded to include ordinal_position.
The stored procedures usp_monitor_scan and usp_monitor_db_merge are the key sprocks. Scan is for your daily batch and Merge is used to initialize a newly added database monitor or to merge changes after you have had a chance to review them. This does not mean changes were prevented from happening before running the merge! The merge simply drops all the flagging and delta records created when change was observed during a Scan.
Additional notes:
The update triggers double-log both the deleted version (B for before) and the inserted version (A for after).
A few tables and the activity logging procedure were not included in the script. I leave reverse-engineering them as an exercise.
/*_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_
VIEWS_DROP
TABLES_DROP
TABLES_INSERTION_POINT
VIEWS_INSERTION_POINT
STORED_PROCEDURES_DROP
STORED_PROCEDURES_INSERTION_POINT
_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_*//*************************************************************************************************
VIEWS_DROP
**************************************************************************************************/USE [UTILITY]
GO
if exists ( select * from sys.objects where name = 'v_monitor_table_schema_before' and type = 'v' )
begin
drop view dbo.[v_monitor_table_schema_before]
end
GO
if exists ( select * from sys.objects where name = 'v_monitor_table_schema_after' and type = 'v' )
begin
drop view dbo.[v_monitor_table_schema_after]
end
GO
if exists ( select * from sys.objects where name = 'v_monitor_table_schemas_last' and type = 'v' )
begin
drop view dbo.[v_monitor_table_schemas_last]
end
GO
if exists ( select * from sys.objects where name = 'v_monitor_object_def_jam' and type = 'v' )
begin
drop view dbo.[v_monitor_object_def_jam]
end
GO
if exists ( select * from sys.objects where name = 'v_monitor_object_definitions' and type = 'v' )
begin
drop view dbo.[v_monitor_object_definitions]
end
GO
if exists ( select * from sys.objects where name = 'v_monitor_object_deltas' and type = 'v' )
begin
drop view dbo.[v_monitor_object_deltas]
end
GO
if exists ( select * from sys.objects where name = 'v_monitor_object_definition_deltas' and type = 'v' )
begin
drop view dbo.[v_monitor_object_definition_deltas]
end
GO
if exists ( select * from sys.objects where name = 'v_monitor_configurations' and type = 'v' )
begin
drop view dbo.[v_monitor_configurations]
end
GO
if exists ( select * from sys.objects where name = 'v_monitor_list' and type = 'v' )
begin
drop view dbo.[v_monitor_list]
end
GO
if exists ( select * from sys.objects where name = 'v_monitor_distribution_list' and type = 'v' )
begin
drop view dbo.[v_monitor_distribution_list]
end
GO
/*************************************************************************************************
TABLES_DROP
**************************************************************************************************/USE [UTILITY]
GO
if exists ( select * from sys.objects where name = 't_monitor_table_schemas_audit' and type = 'u' )
begin
truncate table [t_monitor_table_schemas_audit]
drop table [t_monitor_table_schemas_audit]
end
GO
-- select * from t_monitor_table_schemas
if exists ( select * from sys.objects where name = 't_monitor_table_schemas' and type = 'u' )
begin
truncate table [t_monitor_table_schemas]
drop table [t_monitor_table_schemas]
end
GO
if exists ( select * from sys.objects where name = 't_monitor_objects' and type = 'u' )
begin
truncate table [t_monitor_objects]
drop table [t_monitor_objects]
end
GO
if exists ( select * from sys.objects where name = 't_monitor_object_definitions_audit' and type = 'u' )
begin
truncate table [t_monitor_object_definitions_audit]
drop table [t_monitor_object_definitions_audit]
end
GO
if exists ( select * from sys.objects where name = 't_monitor_object_definitions' and type = 'u' )
begin
truncate table [t_monitor_object_definitions]
drop table [t_monitor_object_definitions]
end
GO
if exists ( select * from sys.objects where name = 't_monitor_od_deltas' and type = 'u' )
begin
truncate table [t_monitor_od_deltas]
drop table [t_monitor_od_deltas]
end
GO
if exists ( select * from sys.objects where name = 't_monitor_ts_deltas' and type = 'u' )
begin
truncate table [t_monitor_ts_deltas]
drop table [t_monitor_ts_deltas]
end
GO
if exists ( select * from sys.objects where name = 't_monitor_facts' and type = 'u' )
begin
truncate table [t_monitor_facts]
drop table [t_monitor_facts]
end
GO
if exists ( select * from sys.objects where name = 't_monitor_dimensions' and type = 'u' )
begin
truncate table [t_monitor_dimensions]
drop table [t_monitor_dimensions]
end
GO
if exists ( select * from sys.objects where name = 't_server_database_list' and type = 'u' )
begin
truncate table [t_server_database_list]
drop table [t_server_database_list]
end
GO
if exists ( select * from sys.objects where name = 't_database_list' and type = 'u' )
begin
truncate table [t_database_list]
drop table [t_database_list]
end
GO
/*************************************************************************************************
TABLES_INSERTION_POINT
**************************************************************************************************/USE [UTILITY]
GO
-- alter table t_monitor_table_schemas alter column transaction_id bigint not null
-- alter table t_monitor_table_schemas alter column transaction_id int not null
create table dbo.[t_monitor_table_schemas](
server_name varchar(128) NOT NULL
,database_name varchar(128) NOT NULL
,table_schema varchar(128) NOT NULL
,table_name varchar(128) NOT NULL
,column_name varchar(128) NOT NULL
,ordinal_position int NOT NULL
,constraint PK_t_monitor_table_schemas
primary key clustered (
server_name
,database_name
,table_schema
,table_name
,ordinal_position
)
,column_default nvarchar(4000) NULL
,is_nullable varchar(3) NOT NULL
,data_type varchar(128) NOT NULL
,character_maximum_length int NULL
,character_octet_length int NULL
,numeric_precision tinyint NULL
,numeric_precision_radix smallint NULL
,numeric_scale int NULL
,datetime_precision smallint NULL
,transaction_id int not null
,create_date datetime not null constraint DF_t_monitor_table_schemas__create_date default ( getdate() )
,batch_date datetime not null constraint DF_t_monitor_table_schemas__batch_date default ( getdate() )
)
GO
create table dbo.[t_monitor_table_schemas_audit](
server_name varchar(128) NOT NULL
,database_name varchar(128) NOT NULL
,table_schema varchar(128) NOT NULL
,table_name varchar(128) NOT NULL
,column_name varchar(128) NOT NULL
,ordinal_position int NOT NULL
,constraint PK_t_monitor_table_schemas_audit
primary key clustered (
server_name
,database_name
,table_schema
,table_name
,ordinal_position
,audit_id
)
,column_default nvarchar(4000) NULL
,is_nullable varchar(3) NOT NULL
,data_type varchar(128) NOT NULL
,character_maximum_length int NULL
,character_octet_length int NULL
,numeric_precision tinyint NULL
,numeric_precision_radix smallint NULL
,numeric_scale int NULL
,datetime_precision smallint NULL
,transaction_id int not null
,create_date datetime not null
,batch_date datetime not null
,audit_date datetime NOT NULL CONSTRAINT DF_t_monitor_table_schemas_audit__audit_date DEFAULT (getdate())
,audit_id int IDENTITY(1,1) NOT NULL
,audit_type char(1) not null
)
GO
create table dbo.[t_monitor_ts_deltas](
server_name varchar(128) NOT NULL
,database_name varchar(128) NOT NULL
,table_schema varchar(128) NOT NULL
,table_name varchar(128) NOT NULL
,column_name varchar(128) NOT NULL
,ordinal_position int NOT NULL
,constraint PK_t_monitor_ts_deltas
primary key clustered (
server_name
,database_name
,table_schema
,table_name
,ordinal_position
,column_name
)
,column_default nvarchar(4000) NULL
,is_nullable varchar(3) NOT NULL
,data_type varchar(128) NOT NULL
,character_maximum_length int NULL
,character_octet_length int NULL
,numeric_precision tinyint NULL
,numeric_precision_radix smallint NULL
,numeric_scale int NULL
,datetime_precision smallint NULL
,batch_date datetime not null constraint DF_t_monitor_ts_deltas__change_date default ( getdate() )
)
GO
create table dbo.[t_monitor_objects] (
server_name varchar(128) not null
,database_name varchar(128) not null
,[schema_name] varchar(50) not null
,[object_name] varchar(128) not null
,type_desc varchar(60) not null
,constraint PK_t_monitor_objects
primary key clustered (
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
)
,batch_date datetime not null constraint DF_t_monitor_objects__change_date default ( getdate() )
,is_active bit not null constraint DF_t_monitor_objects__is_active default ( 0x1 )
,is_ignored bit not null constraint DF_t_monitor_objects__is_ignored default ( 0x0 )
,is_delta bit not null constraint DF_t_monitor_objects__is_delta default ( 0x0 )
,is_ts_delta bit not null constraint DF_t_monitor_objects__is_ts_delta default ( 0x0 )
,is_od_delta bit not null constraint DF_t_monitor_objects__is_od_delta default ( 0x0 )
,notes varchar(max) not null constraint DF_t_monitor_objects__notes default ( '' )
)
GO
create index IX_t_monitor_objects__is_active
on t_monitor_objects ( is_active )
GO
create index IX_t_monitor_objects__is_ignored
on t_monitor_objects ( is_ignored )
GO
create table dbo.[t_monitor_object_definitions] (
server_name varchar(128) NOT NULL
,database_name varchar(128) NOT NULL
,[schema_name] varchar(50) NOT NULL
,[object_name] varchar(128) NOT NULL
,type_desc varchar(60) NOT NULL
,constraint PK_t_monitor_object_definitions
primary key clustered (
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
,colid
)
,colid smallint NOT NULL
,[text] nvarchar(4000) NOT NULL
,transaction_id int not null
,create_date datetime not null constraint DF_t_monitor_object_definitions__create_date default ( getdate() )
,batch_date datetime not null constraint DF_t_monitor_object_definitions__batch_date default ( getdate() )
)
GO
--select top 10 * from sys.objects
create table dbo.[t_monitor_object_definitions_audit] (
server_name varchar(128) NOT NULL
,database_name varchar(128) NOT NULL
,[schema_name] varchar(50) NOT NULL
,[object_name] varchar(128) NOT NULL
,type_desc varchar(60) NOT NULL
,constraint PK_t_monitor_object_definitions_audit
primary key clustered (
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
,colid
,audit_id
)
,colid smallint NOT NULL
,[text] nvarchar(4000) NOT NULL
,transaction_id int not null
,create_date datetime not null
,batch_date datetime not null
,audit_date datetime NOT NULL CONSTRAINT DF_t_monitor_object_definitions_audit__audit_date DEFAULT (getdate())
,audit_id int IDENTITY(1,1) NOT NULL
,audit_type char(1) not null
)
GO
create table dbo.[t_monitor_od_deltas] (
server_name varchar(128) NOT NULL
,database_name varchar(128) NOT NULL
,[schema_name] varchar(50) NOT NULL
,[object_name] varchar(128) NOT NULL
,type_desc varchar(60) NOT NULL
,constraint PK_t_monitor_od_deltas
primary key clustered (
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
,colid
)
,colid smallint NOT NULL
,[text] nvarchar(4000) NOT NULL
,batch_date datetime not null constraint DF_t_monitor_od_deltas__change_date default ( getdate() )
)
GO
USE [UTILITY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[t_database_list](
[database_name] [sysname] NOT NULL,
[is_active] [bit] NOT NULL CONSTRAINT [DF_t_database_list_is_active] DEFAULT ((1)),
CONSTRAINT [pk_t_database_list] PRIMARY KEY CLUSTERED
(
[database_name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
USE [UTILITY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[t_server_database_list](
[server_name] [sysname] NOT NULL,
[database_name] [sysname] NOT NULL,
[is_active] [bit] NOT NULL CONSTRAINT [DF_t_server_database_list_is_active] DEFAULT ((1)),
CONSTRAINT [PK_t_server_database_list] PRIMARY KEY CLUSTERED
(
[server_name]
,[database_name]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
,CONSTRAINT [FK_t_server_database_list__server_name] FOREIGN KEY ([server_name]) references dbo.t_server_list([server_name])
,CONSTRAINT [FK_t_server_database_list__database_name] FOREIGN KEY ([database_name]) references dbo.t_database_list([database_name])
) ON [PRIMARY]
GO
create table dbo.[t_monitor_dimensions](
dimension varchar(50) not null
,[is_active] [bit] NOT NULL CONSTRAINT [DF_t_monitor_dimensions_is_active] DEFAULT ((1))
,CONSTRAINT [PK_t_monitor_dimensions] PRIMARY KEY CLUSTERED
(
dimension
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
GO
insert into dbo.t_monitor_dimensions( dimension )
select dimension = 'monitor_schema'
union all select dimension = 'email_distribution'
GO
create table dbo.[t_monitor_facts](
server_name sysname NOT NULL
,database_name sysname NOT NULL
,dimension varchar(50) not null
,fact varchar(50) not null
,[is_active] [bit] NOT NULL CONSTRAINT [DF_t_monitor_facts_is_active] DEFAULT ((1))
,CONSTRAINT [PK_t_monitor_facts] PRIMARY KEY CLUSTERED
(
server_name
,database_name
,dimension
,fact
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
,CONSTRAINT [FK_t_monitor_facts__t_server_database_list] FOREIGN KEY (server_name, database_name )
references dbo.t_server_database_list( server_name, database_name )
,CONSTRAINT [FK_t_monitor_facts__t_monitor_dimensions] FOREIGN KEY (dimension )
references dbo.t_monitor_dimensions( dimension )
)
GO
--select
--basis_key_1
--,basis_key_2
---- select *
--from dbo.t_ddr_admin_lookups where lookup_group = 'monitored_databases'
--
--
/*************************************************************************************************
VIEWS_INSERTION_POINT
**************************************************************************************************/USE [UTILITY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW dbo.[v_monitor_object_deltas]
/***************************************************************************************************************************************************
Purpose:Observe objects in a delta state.
Overview:Objects are set to delta the first time they appear in a scan or if the underlying definition or schema has changed.
Modified:
20090615 David Korb Created.
***************************************************************************************************************************************************//*_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_
_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_*/--with schemabinding
with schemabinding
as
select
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
,batch_date
,notes
fromdbo.t_monitor_objects
whereis_ignored = 0x0
andis_delta = 0x1
-- [v_monitor_object_deltas]
GO
USE [UTILITY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW dbo.[v_monitor_object_definitions]
/***************************************************************************************************************************************************
Purpose:Current object definitions.
Modified:
20090615 David Korb Created.
***************************************************************************************************************************************************//*_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_
select * from v_monitor_object_definitions
order by
server_name
,database_name
,[schema_name]
,type_desc
,[object_name]
,colid
_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_*/--with schemabinding
with schemabinding
as
select
MOB.server_name
,MOB.database_name
,MOB.[schema_name]
,MOB.[object_name]
,MOB.type_desc
,object_batch_date = MOB.batch_date
,MOB.is_active
,MOB.is_ignored
,MOB.is_delta
,MOB.notes
,OBD.colid
,OBD.[text]
,colid_batch_date = OBD.batch_date
fromdbo.t_monitor_objects as MOB
inner joindbo.t_monitor_object_definitions as OBD
on
OBD.server_name= MOB.server_name
andOBD.database_name= MOB.database_name
andOBD.[schema_name]= MOB.[schema_name]
andOBD.[object_name]= MOB.[object_name]
andOBD.type_desc= MOB.type_desc
-- [v_monitor_object_definitions]
GO
USE [UTILITY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW dbo.[v_monitor_object_definition_deltas]
/***************************************************************************************************************************************************
Purpose:Observe changed object definitions.
Modified:
20090615 David Korb Created.
***************************************************************************************************************************************************//*_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_
select * from v_monitor_object_deltas
order by
server_name
,database_name
,[schema_name]
,type_desc
,[object_name]
,colid
select * from dbo.t_monitor_od_deltas
_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_*/--with schemabinding
with schemabinding
as
select
MOB.server_name
,MOB.database_name
,MOB.[schema_name]
,MOB.[object_name]
,MOB.type_desc
,object_batch_date = MOB.batch_date
,MOB.is_active
,MOB.is_ignored
,MOB.is_delta
,MOB.notes
,ODD.colid
,ODD.[text]
,colid_batch_date = ODD.batch_date
fromdbo.t_monitor_objects as MOB
inner joindbo.t_monitor_od_deltas as ODD
on
ODD.server_name= MOB.server_name
andODD.database_name= MOB.database_name
andODD.[schema_name]= MOB.[schema_name]
andODD.[object_name]= MOB.[object_name]
andODD.type_desc= MOB.type_desc
-- [v_monitor_object_definition_deltas]
GO
USE [UTILITY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW dbo.[v_monitor_object_def_jam]
/***************************************************************************************************************************************************
Purpose:Observe deltas along-side original definitions
Overview:For a more sophisticated comparison of specific objects, select only the old_text column with results to text,
then copy and paste into the left field in a text comparison tool. Next, get the new_text column in the same manner
and paste into the right field of the comparison tool.
Modified:
20090615 David Korb Created.
***************************************************************************************************************************************************//*_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_
select * from v_monitor_object_def_jam
order by
server_name
,database_name
,[schema_name]
,type_desc
,[object_name]
,colid
select * from dbo.v_monitor_object_def_jam
_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_*/--with schemabinding
with schemabinding
as
select
MOB.server_name
,MOB.database_name
,MOB.[schema_name]
,MOB.[object_name]
,MOB.type_desc
,colid = MOB.colid
,old_text = MOB.[text]
,new_text = ODD.[text]
,MOB.object_batch_date
,MOB.notes
,old_colid_batch_date = MOB.colid_batch_date
,new_colid_batch_date = ODD.colid_batch_date
fromdbo.v_monitor_object_definitions as MOB
left joindbo.v_monitor_object_definition_deltas as ODD
on
ODD.server_name= MOB.server_name
andODD.database_name= MOB.database_name
andODD.[schema_name]= MOB.[schema_name]
andODD.[object_name]= MOB.[object_name]
andODD.type_desc= MOB.type_desc
andODD.colid= MOB.colid
whereMOB.is_ignored = 0x0
andexists (
selectsub.server_name
fromdbo.v_monitor_object_definition_deltas as sub
where
sub.server_name= MOB.server_name
andsub.database_name= MOB.database_name
andsub.[schema_name]= MOB.[schema_name]
andsub.[object_name]= MOB.[object_name]
andsub.type_desc= MOB.type_desc
)
-- [v_monitor_object_def_jam]
GO
USE [UTILITY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[v_monitor_list]
/*
select * from dbo.t_server_list
select * from dbo.t_database_list
select * from dbo.t_monitor_dimensions
select * from dbo.t_monitor_facts
*/with schemabinding
as
select
DAF.server_name
,DAF.database_name
fromdbo.t_monitor_dimensionsas DAD
inner joindbo.t_monitor_factsas DAF on DAF.dimension = DAD.dimension
whereDAD.is_active = 0x1
andDAF.is_active = 0x1
andexists (
selectsub.database_name
fromdbo.t_database_list as sub
wheresub.database_name = DAF.database_name
andsub.is_active = 0x1
)
andexists (
selectsub.server_name
fromdbo.t_server_list as sub
wheresub.server_name = DAF.server_name
andsub.is_active = 0x1
)
andDAD.dimension = 'monitor_schema'
-- [v_monitor_list]
GO
USE [UTILITY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[v_monitor_distribution_list]
/*
select * from v_monitor_distribution_list
select * from dbo.t_server_list
select * from dbo.t_database_list
select * from dbo.t_monitor_dimensions
select * from dbo.t_monitor_facts
*/with schemabinding
as
select
DAF.server_name
,DAF.database_name
,distribution_list = DAF.fact
fromdbo.t_monitor_dimensionsas DAD
inner joindbo.t_monitor_factsas DAF on DAF.dimension = DAD.dimension
whereDAD.is_active = 0x1
andDAF.is_active = 0x1
andexists (
selectsub.database_name
fromdbo.t_database_list as sub
wheresub.database_name = DAF.database_name
andsub.is_active = 0x1
)
andexists (
selectsub.server_name
fromdbo.t_server_list as sub
wheresub.server_name = DAF.server_name
andsub.is_active = 0x1
)
andDAD.dimension = 'email_distribution'
-- [v_monitor_distribution_list]
GO
USE [UTILITY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW dbo.[v_monitor_configurations]
/*
select * from dbo.t_server_list
select * from dbo.t_database_list
select * from dbo.t_monitor_dimensions
select * from dbo.t_monitor_facts
*/with schemabinding
as
select
MOL.server_name
,MOL.database_name
,MDL.distribution_list
fromdbo.v_monitor_listas MOL
left joindbo.v_monitor_distribution_listas MDL
on MDL.server_name = MOL.server_name
and MDL.database_name = MOL.database_name
-- [v_monitor_configurations]
GO
USE [UTILITY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW dbo.[v_monitor_table_schemas_last]
/***************************************************************************************************************************************************
Purpose:Shows the last version of every schema in the audit table.
Modified:
20090615 David Korb Created.
***************************************************************************************************************************************************//*_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_
select count(*) from t_monitor_table_schemas_audit
select count(*) from v_monitor_table_schemas_last
select count(*) from t_monitor_table_schemas
select * from v_monitor_table_schemas_last
select * from v_monitor_table_schemas_last where audit_transaction_id <> 5541
select * from v_monitor_table_schemas_last where audit_transaction_id = 5541
select * from v_monitor_table_schemas_last where audit_transaction_id > 5541
select * from t_monitor_table_schemas_audit where table_name = 'v_monitor_table_schemas_last' order by ordinal_position
select * from v_monitor_table_schemas_last where table_name = 'v_monitor_table_schemas_last' order by ordinal_position
select*
fromdbo.t_monitor_table_schemas_audit
wheretable_name = 'v_monitor_table_schemas_last'
order by ordinal_position
_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_*/--with schemabinding
with schemabinding
as
with LATEST_VERSION (
server_name
,database_name
,table_schema
,table_name
,ordinal_position
,audit_id
) as (
select
server_name
,database_name
,table_schema
,table_name
,ordinal_position
,audit_id = max( audit_id )
fromdbo.t_monitor_table_schemas_audit as AUD
whereaudit_type <> 'D'
group by
server_name
,database_name
,table_schema
,table_name
,ordinal_position
)
select
server_name
,database_name
,table_schema
,table_name
,column_name
,ordinal_position
,column_default
,is_nullable
,data_type
,character_maximum_length
,character_octet_length
,numeric_precision
,numeric_precision_radix
,numeric_scale
,datetime_precision
,transaction_id
,create_date
,batch_date
,audit_date
,audit_id
,audit_type
fromdbo.t_monitor_table_schemas_audit as AUD
whereexists (
selectsub.server_name
fromLATEST_VERSION as sub
wheresub.server_name= AUD.server_name
andsub.database_name= AUD.database_name
andsub.table_schema= AUD.table_schema
andsub.table_name= AUD.table_name
andsub.ordinal_position= AUD.ordinal_position
andsub.audit_id= AUD.audit_id
)
-- [v_monitor_table_schemas_last]
GO
USE [UTILITY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[v_monitor_table_schema_after]
/***************************************************************************************************************************************************
Purpose:Observe new table schema deltas.
Modified:
20090615 David Korb Created.
***************************************************************************************************************************************************//*_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_
select * from v_monitor_table_schema_after
_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_*/--with schemabinding
with schemabinding
as
select
server_name
,database_name
,table_schema
,table_name
,column_name
,ordinal_position
,column_default
,is_nullable
,data_type
,character_maximum_length
,character_octet_length
,numeric_precision
,numeric_precision_radix
,numeric_scale
,datetime_precision
fromdbo.t_monitor_ts_deltas as MTS
whereexists (
selectsub.server_name
fromdbo.v_monitor_object_deltas as sub
wheresub.server_name= MTS.server_name
andsub.database_name= MTS.database_name
andsub.[schema_name]= MTS.table_schema
andsub.[object_name]= MTS.table_name
)
-- [v_monitor_table_schema_after]
GO
USE [UTILITY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[v_monitor_table_schema_before]
/***************************************************************************************************************************************************
Purpose:Get previous table schema for objects that have new deltas.
Modified:
20090615 David Korb Created.
***************************************************************************************************************************************************//*_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_
select * from v_monitor_table_schema_after
select * from v_monitor_object_deltas
_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_*/--with schemabinding
with schemabinding
as
select
server_name
,database_name
,table_schema
,table_name
,column_name
,ordinal_position
,column_default
,is_nullable
,data_type
,character_maximum_length
,character_octet_length
,numeric_precision
,numeric_precision_radix
,numeric_scale
,datetime_precision
fromdbo.t_monitor_table_schemas as MTS
whereexists (
selectsub.server_name
fromdbo.v_monitor_table_schema_after as sub
wheresub.server_name= MTS.server_name
andsub.database_name= MTS.database_name
andsub.table_schema= MTS.table_schema
andsub.table_name= MTS.table_name
andsub.ordinal_position= MTS.ordinal_position
)
-- [v_monitor_table_schema_before]
GO
/*************************************************************************************************
STORED_PROCEDURES_DROP
**************************************************************************************************/USE [UTILITY]
GO
if exists ( select * from sys.objects where name = 'TR_T_MONITOR_OBJECT_DEFINITIONS_DELETE' and type = 'TR' )
begin
drop trigger [TR_T_MONITOR_OBJECT_DEFINITIONS_DELETE]
end
GO
if exists ( select * from sys.objects where name = 'TR_T_MONITOR_OBJECT_DEFINITIONS_UPDATE' and type = 'TR' )
begin
drop trigger [TR_T_MONITOR_OBJECT_DEFINITIONS_UPDATE]
end
GO
if exists ( select * from sys.objects where name = 'TR_T_MONITOR_OBJECT_DEFINITIONS_INSERT' and type = 'TR' )
begin
drop trigger [TR_T_MONITOR_OBJECT_DEFINITIONS_INSERT]
end
GO
if exists ( select * from sys.objects where name = 'TR_T_MONITOR_TABLE_SCHEMAS_DELETE' and type = 'TR' )
begin
drop trigger [TR_T_MONITOR_TABLE_SCHEMAS_DELETE]
end
GO
if exists ( select * from sys.objects where name = 'TR_T_MONITOR_TABLE_SCHEMAS_UPDATE' and type = 'TR' )
begin
drop trigger [TR_T_MONITOR_TABLE_SCHEMAS_UPDATE]
end
GO
if exists ( select * from sys.objects where name = 'TR_T_MONITOR_TABLE_SCHEMAS_INSERT' and type = 'TR' )
begin
drop trigger [TR_T_MONITOR_TABLE_SCHEMAS_INSERT]
end
GO
if exists ( select * from sys.objects where name = 'usp_monitor_db_merge' and type = 'p' )
begin
drop procedure [usp_monitor_db_merge]
end
GO
if exists ( select * from sys.objects where name = 'usp_monitor_scan' and type = 'p' )
begin
drop procedure [usp_monitor_scan]
end
GO
if exists ( select * from sys.objects where name = 'usp_monitor_pit_tables' and type = 'p' )
begin
drop procedure [usp_monitor_pit_tables]
end
GO
if exists ( select * from sys.objects where name = 'usp_monitor_pit_objects' and type = 'p' )
begin
drop procedure [usp_monitor_pit_objects]
end
GO
/*************************************************************************************************
STORED_PROCEDURES_INSERTION_POINT
**************************************************************************************************/USE [UTILITY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_monitor_pit_objects](
@server_name varchar(128) = null
,@database_name varchar(128) = null
,@schema_name varchar(128) = null
,@object_name varchar(128)
,@batch_date datetime
)
/***************************************************************************************************************************************************
Purpose:Get point in time view of object definitions
Modified:
20090615 David Korb Created.
***************************************************************************************************************************************************//*_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_
EXEC dbo.usp_monitor_pit_objects
@server_name = null
,@database_name = null
,@schema_name = null
,@object_name = 'v_monitor_table_schemas_last'
,@batch_date = '2009-06-20 12:21:53.013'
_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_*/as
set nocount on
begin
select
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
,colid
,[text]
,transaction_id
,create_date
,batch_date
,audit_date
,audit_id
,audit_type
fromdbo.t_monitor_object_definitions_audit
where
audit_id in (
selectmax( sub.audit_id )
fromdbo.t_monitor_object_definitions_audit as sub
where
sub.audit_type not in ( 'B', 'D' )
andsub.server_name = coalesce( @server_name, sub.server_name )
andsub.database_name = coalesce( @database_name, sub.database_name )
andsub.[schema_name] = coalesce( @schema_name, sub.[schema_name] )
andsub.[object_name] = @object_name
andsub.batch_date <= @batch_date
group by
sub.server_name
,sub.database_name
,sub.[schema_name]
,sub.[object_name]
,sub.colid
)
order by
server_name
,database_name
,[object_name]
,[schema_name]
,colid
return @@error
end -- [usp_monitor_pit_objects]
GO
USE [UTILITY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_monitor_pit_tables](
@server_name varchar(128) = null
,@database_name varchar(128) = null
,@table_schema varchar(128) = null
,@table_name varchar(128)
,@batch_date datetime
)
/***************************************************************************************************************************************************
Purpose:Get point in time view table schema
Modified:
20090615 David Korb Created.
***************************************************************************************************************************************************//*_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_
EXEC dbo.usp_monitor_pit_tables
@server_name = null
,@database_name = null
,@table_schema = null
,@table_name = 'v_monitor_table_schemas_last'
,@batch_date = '2009-06-20 12:21:53.013'
_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_*/as
set nocount on
begin
select
server_name
,database_name
,table_schema
,table_name
,column_name
,ordinal_position
,column_default
,is_nullable
,data_type
,character_maximum_length
,character_octet_length
,numeric_precision
,numeric_precision_radix
,numeric_scale
,datetime_precision
,transaction_id
,create_date
,batch_date
,audit_date
,audit_id
,audit_type
fromdbo.t_monitor_table_schemas_audit
where
audit_id in (
selectmax( sub.audit_id )
fromdbo.t_monitor_table_schemas_audit as sub
where
sub.audit_type not in ( 'B', 'D' )
andsub.server_name = coalesce( @server_name, sub.server_name )
andsub.database_name = coalesce( @database_name, sub.database_name )
andsub.table_schema = coalesce( @table_schema, sub.table_schema )
andsub.table_name = @table_name
andsub.batch_date <= @batch_date
group by
sub.server_name
,sub.database_name
,sub.table_schema
,sub.table_name
,sub.ordinal_position
)
order by
server_name
,database_name
,table_schema
,table_name
,ordinal_position
return @@error
end -- [usp_monitor_pit_tables]
GO
USE [UTILITY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_monitor_scan]
(
@transaction_id int = 0
,@system_date datetime = null
,@output_print int = 0
,@test_run int = 0
)
/***************************************************************************************************************************************************
Purpose:Daily batch to scan for change.
Overview:Scans monitored DBs for changes in object definitions in the system view, sys.syscomments, and
changes to table schema found in the information_schema.columns (tables and views) and
information_schema.routine_columns (table valued functions) system views.
Modified:
20090615 David Korb Created.
***************************************************************************************************************************************************//*_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_
DECLARE_VARIABLES
GET_DATABASES
GET_OBJECTS
GET_OBJECT_DEFINITIONS
GET_TABLE_SCHEMA
DE_ACTIVATE_MISSING_OBJECTS
RE_CREATED_RE_ACTIVATED_OBJECTS
INSERT_NEW_OBJECTS
INSERT_OBJECT_DEFINITION_DELTAS
INSERT_TABLE_SCHEMA_DELTAS
FLAG_OBJECT_DEFINITIONS_HAVING_DELTAS
FLAG_TABLE_OBJECTS_HAVING_DELTAS
LOG_THE_ERROR_RAISE_THE_ERROR_AND_RETURN
_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_*//*_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_
select * from dbo.SNAP_WCV_SOURCE_SYSTEM_MASTER
exec dbo.usp_monitor_scan
@output_print = 1
,@test_run = 1
_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_*/as
set nocount on
begin
/**************************************************************************************************
DECLARE_VARIABLES
***************************************************************************************************/declare @error_number int
declare @error_severity int
declare @error_state int
declare @error_message nvarchar(2048)
declare @sql_string varchar(4000)
declare @counter int
declare @cr_lf char(2)
declare @default_severity int
declare @server_name sysname
declare @database_name sysname
declare @procedure_name sysname
declare @table_name sysname
declare @activity varchar(255)
declare @row_count int
select @cr_lf = char(13) + char(10)
if @test_run = 1 and @output_print = 1
begin
set @output_print = 0
end
create table #_ums_servers_databases (
id int identity
,server_name sysname NOT NULL
,database_name sysname NOT NULL
)
-- select * from #_ums_objects
create table #_ums_objects (
server_name varchar(128) not null
,database_name varchar(128) not null
,[schema_name] varchar(50) not null
,[object_name] varchar(128) not null
,type_desc varchar(60) not null
)
create table #_ums_object_definitions (
server_name varchar(128) NOT NULL
,database_name varchar(128) NOT NULL
,[schema_name] varchar(50) NOT NULL
,[object_name] varchar(128) NOT NULL
,type_desc varchar(60) NOT NULL
,colid smallint NOT NULL
,[text] nvarchar(4000) NOT NULL
)
create table #_ums_monitor_table_schema(
server_name varchar(128) NOT NULL
,database_name varchar(128) NOT NULL
,table_schema varchar(128) NOT NULL
,table_name varchar(128) NOT NULL
,column_name varchar(128) NOT NULL
,ordinal_position int NOT NULL
,column_default nvarchar(4000) NULL
,is_nullable varchar(3) NOT NULL
,data_type varchar(128) NOT NULL
,character_maximum_length int NULL
,character_octet_length int NULL
,numeric_precision tinyint NULL
,numeric_precision_radix smallint NULL
,numeric_scale int NULL
,datetime_precision smallint NULL
)
select @system_date = coalesce( @system_date, current_timestamp )
select @procedure_name = OBJECT_NAME( @@PROCID )
set @activity = 'BEGIN______' + upper( @procedure_name )
exec dbo.usp_util_activity_logging
@transaction_id = @transaction_id output
,@procedure_name = @procedure_name
,@activity = @activity
,@output_print = @output_print
select @activity = 'Get server database list'
insert into #_ums_servers_databases ( server_name, database_name )
select
server_name
,database_name
-- select *
fromdbo.v_monitor_configurations
create unique clustered index IX_#_ums_servers_databases
on #_ums_servers_databases ( server_name, database_name )
/**************************************************************************************************
GET_OBJECTS
***************************************************************************************************/select @counter = 1, @row_count = count(*) from #_ums_servers_databases
while @counter <= @row_count
begin
select
@server_name = server_name
,@database_name = database_name
from#_ums_servers_databases
whereid = @counter
set @activity = 'EXEC ( @sql_string ): insert #_ums_objects'
set @sql_string = ''
set @sql_string = 'insert into #_ums_objects (
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
) ' + char(13)
set @sql_string = @sql_string +
'
select
server_name = ''' + @server_name + '''
,database_name = ''' + @database_name + '''
,[schema_name] = SCH.[name]
,[object_name] = SOB.[name]
,SOB.type_desc
from[' + @server_name + '].[' + @database_name + '].sys.schemas as SCH with (nolock)
inner join[' + @server_name + '].[' + @database_name + '].sys.objects SOB with (nolock) on SOB.[schema_id] = SCH.[schema_id]
whereSOB.is_ms_shipped = 0
'
if @test_run = 1
begin
print @sql_string
end
else
begin
begin try
execute ( @sql_string )
end try
begin catch
select @activity = @activity + ' against server: [' + @server_name + ']'
goto log_error
end catch
end
set @counter = @counter + 1
end
select @row_count = count(*), @table_name = '#_ums_objects' from #_ums_objects
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'I'
,@row_count= @row_count
,@output_print= @output_print
begin try
select @activity = 'Create unique clustered index on #_ums_objects'
create unique clustered index IX_#_ums_objects
on #_ums_objects (
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
)
end try
begin catch
goto log_error
end catch
/**************************************************************************************************
GET_OBJECT_DEFINITIONS
***************************************************************************************************/select @counter = 1, @row_count = count(*) from #_ums_servers_databases
while @counter <= @row_count
begin
select
@server_name = server_name
,@database_name = database_name
from#_ums_servers_databases
whereid = @counter
set @activity = 'EXEC ( @sql_string ): insert #_ums_object_definitions'
set @sql_string = ''
set @sql_string = 'insert into #_ums_object_definitions (
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
,colid
,[text]
) ' + char(13)
set @sql_string = @sql_string +
'
select
server_name = ''' + @server_name + '''
,database_name = ''' + @database_name + '''
,[schema_name] = SCH.[name]
,[object_name] = SOB.[name]
,SOB.type_desc
,SCO.colid
,SCO.[text]
from[' + @server_name + '].[' + @database_name + '].sys.schemas as SCH with (nolock)
inner join[' + @server_name + '].[' + @database_name + '].sys.objects SOB with (nolock) on SOB.[schema_id] = SCH.[schema_id]
inner join[' + @server_name + '].[' + @database_name + '].sys.syscomments SCO with (nolock) on SCO.id = SOB.[object_id]
whereSOB.is_ms_shipped = 0
'
if @test_run = 1
begin
print @sql_string
end
else
begin
begin try
execute ( @sql_string )
end try
begin catch
select @activity = @activity + ' against server: [' + @server_name + ']'
goto log_error
end catch
end
set @counter = @counter + 1
end
select @row_count = count(*), @table_name = '#_ums_object_definitions' from #_ums_object_definitions
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'I'
,@row_count= @row_count
,@output_print= @output_print
begin try
select @activity = 'Create unique clustered index on #_ums_object_definitions'
create unique clustered index IX_#_ums_object_definitions
on #_ums_object_definitions (
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
,colid
)
end try
begin catch
goto log_error
end catch
/**************************************************************************************************
GET_TABLE_SCHEMA
***************************************************************************************************/select @counter = 1, @row_count = count(*) from #_ums_servers_databases
while @counter <= @row_count
begin
select
@server_name = server_name
,@database_name = database_name
from#_ums_servers_databases
whereid = @counter
set @activity = 'EXEC ( @sql_string ): insert #_ums_monitor_table_schema'
set @sql_string = ''
set @sql_string = 'insert into #_ums_monitor_table_schema (
server_name
,database_name
,table_schema
,table_name
,column_name
,ordinal_position
,column_default
,is_nullable
,data_type
,character_maximum_length
,character_octet_length
,numeric_precision
,numeric_precision_radix
,numeric_scale
,datetime_precision
) ' + char(13)
set @sql_string = @sql_string +
'
select
server_name = ''' + @server_name + '''
,database_name = ''' + @database_name + '''
,table_schema = cast( TABLE_SCHEMA as varchar(128) )
,table_name = cast( table_name as varchar(128) )
,column_name = cast( column_name as varchar(128) )
,ordinal_position
,column_default = cast( column_default as varchar(max))
,is_nullable
,data_type = cast( data_type as varchar(128) )
,character_maximum_length
,character_octet_length
,numeric_precision
,numeric_precision_radix
,numeric_scale
,datetime_precision
from[' + @server_name + '].[' + @database_name + '].information_schema.columns
union all
select
server_name = ''' + @server_name + '''
,database_name = ''' + @database_name + '''
,table_schema = cast( TABLE_SCHEMA as varchar(128) )
,table_name = cast( table_name as varchar(128) )
,column_name = cast( column_name as varchar(128) )
,ordinal_position
,column_default = cast( column_default as varchar(max))
,is_nullable
,data_type = cast( data_type as varchar(128) )
,character_maximum_length
,character_octet_length
,numeric_precision
,numeric_precision_radix
,numeric_scale
,datetime_precision
from[' + @server_name + '].[' + @database_name + '].information_schema.routine_columns
'
if @test_run = 1
begin
print @sql_string
end
else
begin
begin try
execute ( @sql_string )
end try
begin catch
select @activity = @activity + ' against server: [' + @server_name + ']'
goto log_error
end catch
end
set @counter = @counter + 1
end
select @row_count = count(*), @table_name = '#_ums_monitor_table_schema' from #_ums_monitor_table_schema
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'I'
,@row_count= @row_count
,@output_print= @output_print
begin try
select @activity = 'Create unique clustered index on #_ums_monitor_table_schema'
create unique clustered index IX_#_ums_monitor_table_schema
on #_ums_monitor_table_schema (
server_name
,database_name
,table_schema
,table_name
,ordinal_position
,column_name
)
end try
begin catch
goto log_error
end catch
if @test_run = 1 goto test_run
/**************************************************************************************************
DE_ACTIVATE_MISSING_OBJECTS
***************************************************************************************************/-- select count(*) from sys.objects
begin try
select @activity = 'Update to inactive'
-- select * from dbo.t_monitor_objects where is_active = 0
-- declare @system_date datetime, @cr_lf char(2); select @system_date = getdate(),@cr_lf = char(13) + char(10)
updateDOB
set
DOB.batch_date = @system_date
,DOB.is_active = 0x0
,DOB.is_delta = 0x1
,DOB.notes =
convert( varchar, @system_date, 121 )
+ ' -- Object not found: set to inactive.' + @cr_lf + @cr_lf
+ DOB.notes
-- select count(*) -- select *
fromdbo.t_monitor_objects as DOB
wherenot exists (
select*
from#_ums_objectsas sub
where
sub.server_name = DOB.server_name
andsub.database_name = DOB.database_name
andsub.[schema_name] = DOB.[schema_name]
andsub.[object_name] = DOB.[object_name]
andsub.type_desc = DOB.type_desc
)
andDOB.is_active = 0x1
andDOB.is_ignored = 0x0
select @row_count = @@rowcount, @table_name = 't_monitor_objects'
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'U'
,@row_count= @row_count
,@output_print= @output_print
end try
begin catch
goto log_error
end catch
/**************************************************************************************************
RE_CREATED_RE_ACTIVATED_OBJECTS
***************************************************************************************************/begin try
select @activity = 'Update object recreated-reactivated'
-- declare @system_date datetime, @cr_lf char(2); select @system_date = getdate(),@cr_lf = char(13) + char(10)
updateDOB
set
DOB.batch_date = @system_date
,DOB.is_active = 0x1
,DOB.is_delta = 0x1
,DOB.notes =
convert( varchar, @system_date, 121 )
+ ' -- Reanimated object: flagged active.' + @cr_lf + @cr_lf
+ DOB.notes
-- select count(*) -- select *
fromdbo.t_monitor_objectsas DOB
inner join#_ums_objectsas dub on dub.server_name = DOB.server_name
anddub.database_name = DOB.database_name
anddub.[schema_name] = DOB.[schema_name]
anddub.[object_name] = DOB.[object_name]
anddub.type_desc = DOB.type_desc
whereDOB.is_active = 0x0
andDOB.is_ignored = 0x0
select @row_count = @@rowcount, @table_name = 't_monitor_objects'
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'U'
,@row_count= @row_count
,@output_print= @output_print
end try
begin catch
goto log_error
end catch
/**************************************************************************************************
INSERT_NEW_OBJECTS
***************************************************************************************************/begin try
select @activity = 'Insert objects'
insert into dbo.t_monitor_objects(
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
,is_delta
,notes
)
select
OBJ.server_name
,OBJ.database_name
,OBJ.[schema_name]
,OBJ.[object_name]
,OBJ.type_desc
-- New objects are deltas that require attention.
,is_delta = 0x1
,notes = '-- New object appearance: flagged delta.'
-- select *
from#_ums_objects as OBJ
wherenot exists (
select*
fromdbo.t_monitor_objects as sub
wheresub.server_name= OBJ.server_name
andsub.database_name= OBJ.database_name
andsub.[schema_name]= OBJ.[schema_name]
andsub.[object_name]= OBJ.[object_name]
andsub.type_desc= OBJ.type_desc
)
select @row_count = @@rowcount, @table_name = 't_monitor_objects'
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'I'
,@row_count= @row_count
,@output_print= @output_print
end try
begin catch
goto log_error
end catch
/**************************************************************************************************
INSERT_OBJECT_DEFINITION_DELTAS
***************************************************************************************************/begin try
--
-- select * from dbo.t_monitor_od_deltas
--
select @activity = 'Insert definition deltas'
insert into dbo.t_monitor_od_deltas(
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
,colid
,[text]
)
select
TOD.server_name
,TOD.database_name
,TOD.[schema_name]
,TOD.[object_name]
,TOD.type_desc
,TOD.colid
,TOD.[text]
-- select *
from#_ums_object_definitions as TOD
wherenot exists (
select*
fromdbo.t_monitor_od_deltas as sub
where
sub.server_name= TOD.server_name
andsub.database_name= TOD.database_name
andsub.[schema_name]= TOD.[schema_name]
andsub.[object_name]= TOD.[object_name]
andsub.type_desc= TOD.type_desc
andsub.colid= TOD.colid
)
andnot exists (
select*
fromdbo.t_monitor_object_definitions as OBD
where
OBD.server_name= TOD.server_name
andOBD.database_name= TOD.database_name
andOBD.[schema_name]= TOD.[schema_name]
andOBD.[object_name]= TOD.[object_name]
andOBD.type_desc= TOD.type_desc
andOBD.colid= TOD.colid
andOBD.[text]= TOD.[text]
)
select @row_count = @@rowcount, @table_name = 't_monitor_od_deltas'
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'I'
,@row_count= @row_count
,@output_print= @output_print
end try
begin catch
goto log_error
end catch
/**************************************************************************************************
INSERT_TABLE_SCHEMA_DELTAS
***************************************************************************************************/begin try
-- select * from dbo.t_monitor_ts_deltas
select @activity = 'Insert table deltas'
insert into dbo.t_monitor_ts_deltas(
server_name
,database_name
,table_schema
,table_name
,column_name
,ordinal_position
,column_default
,is_nullable
,data_type
,character_maximum_length
,character_octet_length
,numeric_precision
,numeric_precision_radix
,numeric_scale
,datetime_precision
)
select
TTS.server_name
,TTS.database_name
,TTS.table_schema
,TTS.table_name
,TTS.column_name
,TTS.ordinal_position
,TTS.column_default
,TTS.is_nullable
,TTS.data_type
,TTS.character_maximum_length
,TTS.character_octet_length
,TTS.numeric_precision
,TTS.numeric_precision_radix
,TTS.numeric_scale
,TTS.datetime_precision
-- select *
from#_ums_monitor_table_schema as TTS
where
not exists (
select*
fromdbo.t_monitor_ts_deltas as sub
where
sub.server_name= TTS.server_name
andsub.database_name= TTS.database_name
andsub.table_schema= TTS.table_schema
andsub.table_name= TTS.table_name
andsub.ordinal_position= TTS.ordinal_position
andsub.column_name= TTS.column_name
)
andnot exists (
select*
fromdbo.t_monitor_table_schemas as MTS
whereMTS.server_name= TTS.server_name
andMTS.database_name= TTS.database_name
andMTS.table_schema= TTS.table_schema
andMTS.table_name= TTS.table_name
andMTS.ordinal_position= TTS.ordinal_position
andMTS.column_name= TTS.column_name
andMTS.is_nullable= TTS.is_nullable
andMTS.data_type= TTS.data_type
and ( MTS.column_default is null AND TTS.column_default is null
OR MTS.column_default = TTS.column_default
)
and ( MTS.character_maximum_length is null AND TTS.character_maximum_length is null
OR MTS.character_maximum_length = TTS.character_maximum_length
)
and ( MTS.character_octet_length is null AND TTS.character_octet_length is null
OR MTS.character_octet_length = TTS.character_octet_length
)
and ( MTS.numeric_precision is null AND TTS.numeric_precision is null
OR MTS.numeric_precision = TTS.numeric_precision
)
and ( MTS.numeric_precision_radix is null AND TTS.numeric_precision_radix is null
OR MTS.numeric_precision_radix = TTS.numeric_precision_radix
)
and ( MTS.numeric_scale is null AND TTS.numeric_scale is null
OR MTS.numeric_scale = TTS.numeric_scale
)
and ( MTS.datetime_precision is null AND TTS.datetime_precision is null
OR MTS.datetime_precision = TTS.datetime_precision
)
)
select @row_count = @@rowcount, @table_name = 't_monitor_ts_deltas'
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'I'
,@row_count= @row_count
,@output_print= @output_print
end try
begin catch
goto log_error
end catch
/**************************************************************************************************
FLAG_OBJECT_DEFINITIONS_HAVING_DELTAS
***************************************************************************************************/begin try
--
-- select top 100 * from dbo.t_monitor_table_schemas
--
-- select top 100 * from dbo.t_monitor_table_schemas
--
select @activity = 'Update deltad object definitions'
-- declare @system_date datetime, @cr_lf char(2); select @system_date = getdate(),@cr_lf = char(13) + char(10)
updateMOB
set
MOB.batch_date = @system_date
,MOB.is_delta = 0x1
,MOB.is_od_delta = 0x1
,MOB.notes =
case
when MOB.is_delta = 0x0
then
convert( varchar, @system_date, 121 )
+ ' -- Changed object definition: flagged od delta.' + @cr_lf + @cr_lf
else ''
end
+ MOB.notes
-- select count(*) -- select *
fromdbo.t_monitor_objects as MOB
whereMOB.is_ignored = 0x0
andMOB.is_od_delta = 0x0
andexists (
select*
fromdbo.t_monitor_od_deltas as ODD
whereODD.server_name= MOB.server_name
andODD.database_name= MOB.database_name
andODD.[schema_name]= MOB.[schema_name]
andODD.[object_name]= MOB.[object_name]
andODD.type_desc= MOB.type_desc
)
select @row_count = @@rowcount, @table_name = 't_monitor_objects'
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'U'
,@row_count= @row_count
,@output_print= @output_print
end try
begin catch
goto log_error
end catch
/**************************************************************************************************
FLAG_TABLE_OBJECTS_HAVING_DELTAS
***************************************************************************************************/begin try
--
-- select top 100 * from dbo.t_monitor_table_schemas
--
-- select top 100 * from dbo.t_monitor_table_schemas
--
select @activity = 'Update deltad table objects'
-- declare @system_date datetime, @cr_lf char(2); select @system_date = getdate(),@cr_lf = char(13) + char(10)
updateMOB
set
MOB.batch_date = @system_date
,MOB.is_delta = 0x1
,MOB.is_ts_delta = 0x1
,MOB.notes =
case
when MOB.is_delta = 0x0
then
convert( varchar, @system_date, 121 )
+ ' -- Changed table schema: flagged ts delta.' + @cr_lf + @cr_lf
else ''
end
+ MOB.notes
-- select count(*) -- select *
fromdbo.t_monitor_objects as MOB
whereMOB.is_ignored = 0x0
andMOB.is_ts_delta = 0x0
andexists (
select*
fromdbo.t_monitor_ts_deltas as TSD
whereTSD.server_name= MOB.server_name
andTSD.database_name= MOB.database_name
andTSD.table_schema= MOB.[schema_name]
andTSD.table_name= MOB.[object_name]
)
select @row_count = @@rowcount, @table_name = 't_monitor_objects'
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'U'
,@row_count= @row_count
,@output_print= @output_print
end try
begin catch
goto log_error
end catch
test_run:
/**************************************************************************************************
THE_END
***************************************************************************************************/set @activity = 'END________' + upper( @procedure_name )
exec dbo.usp_util_activity_logging
@transaction_id = @transaction_id
,@procedure_name = @procedure_name
,@activity = @activity
,@output_print = @output_print
return @@error
/**************************************************************************************************
LOG_THE_ERROR_RAISE_THE_ERROR_AND_RETURN
***************************************************************************************************/log_error:
select @activity = 'Error: ' + @activity
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@activity= @activity
,@error_number= @error_number output
,@error_severity= @error_severity output
,@error_state= @error_state output
,@error_message= @error_message output
,@output_print= @output_print
select @error_message = @activity + '::' + @error_message
raiserror ( @error_message, @error_severity, @error_state ) with nowait
return @error_number
end -- [usp_monitor_scan]
GO
USE [UTILITY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_monitor_db_merge]
(
@transaction_id int = 0
,@server_name sysname = null
,@database_name sysname = null
,@system_date datetime = null
,@output_print int = 0
,@test_run int = 0
)
/***************************************************************************************************************************************************
Purpose:For a given monitored DB, merge all new definitions and schemas into the monitor database. This resets the monitor
and stops email alerts, at least until another object is modified.
Modified:
20090615 David Korb Created.
***************************************************************************************************************************************************//*_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_
DECLARE_VARIABLES
GET_OBJECTS
GET_OBJECT_DEFINITIONS
GET_TABLE_SCHEMA
DELETE_OD_DELTAS
DELETE_TS_DELTAS
DELETE_OBJECTS
INSERT_OBJECTS
DELETE_OBJECT_MISSING_DEFINITIONS
UPDATE_OBJECT_DEFINITION_TEXT_DELTAS
INSERT_NEW_OBJECT_DEFINITIONS
DELETE_MISSING_TABLE_SKHEMAS
UPDATE_CHANGED_TABLE_SKHEMAS
INSERT_NEW_TABLE_SKHEMAS
LOG_THE_ERROR_RAISE_THE_ERROR_AND_RETURN
_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_*//*_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_
exec dbo.usp_monitor_db_merge
@server_name = 'WSWZP4254'
,@database_name = 'ICM_PROCESS'
,@output_print = 1
,@test_run = 1
_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_*/as
set nocount on
begin
/**************************************************************************************************
DECLARE_VARIABLES
***************************************************************************************************/declare @default_error int
declare @error_number int
declare @error_severity int
declare @error_state int
declare @error_message nvarchar(2048)
declare @sql_string varchar(4000)
declare @counter int
declare @cr_lf char(2)
declare @procedure_name sysname
declare @table_name sysname
declare @activity varchar(255)
declare @row_count int
select @cr_lf = char(13) + char(10), @default_error = 50001
select @system_date = coalesce( @system_date, current_timestamp )
select @procedure_name = OBJECT_NAME( @@PROCID )
if @test_run = 1 and @output_print = 1
begin
set @output_print = 0
end
-- select * from #_mdm_objects
create table #_mdm_objects (
server_name varchar(128) not null
,database_name varchar(128) not null
,[schema_name] varchar(50) not null
,[object_name] varchar(128) not null
,type_desc varchar(60) not null
)
create table #_mdm_object_definitions (
server_name varchar(128) NOT NULL
,database_name varchar(128) NOT NULL
,[schema_name] varchar(50) NOT NULL
,[object_name] varchar(128) NOT NULL
,type_desc varchar(60) NOT NULL
,colid smallint NOT NULL
,[text] nvarchar(4000) NOT NULL
)
create table #_mdm_monitor_table_schema(
server_name varchar(128) NOT NULL
,database_name varchar(128) NOT NULL
,table_schema varchar(128) NOT NULL
,table_name varchar(128) NOT NULL
,column_name varchar(128) NOT NULL
,ordinal_position int NOT NULL
,column_default nvarchar(4000) NULL
,is_nullable varchar(3) NOT NULL
,data_type varchar(128) NOT NULL
,character_maximum_length int NULL
,character_octet_length int NULL
,numeric_precision tinyint NULL
,numeric_precision_radix smallint NULL
,numeric_scale int NULL
,datetime_precision smallint NULL
)
set @activity = 'BEGIN______' + upper( @procedure_name )
exec dbo.usp_util_activity_logging
@transaction_id = @transaction_id output
,@procedure_name = @procedure_name
,@activity = @activity
,@output_print = @output_print
if not exists(
select*
fromdbo.v_monitor_configurations
whereserver_name = @server_name
anddatabase_name = @database_name
)
begin
select @error_number = @default_error
goto log_error
end
/**************************************************************************************************
GET_OBJECTS
***************************************************************************************************/set @activity = 'EXEC ( @sql_string ): insert #_mdm_objects'
set @sql_string = ''
set @sql_string = 'insert into #_mdm_objects (
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
) ' + char(13)
set @sql_string = @sql_string +
'
select
server_name = ''' + @server_name + '''
,database_name = ''' + @database_name + '''
,[schema_name] = SCH.[name]
,[object_name] = SOB.[name]
,SOB.type_desc
from[' + @server_name + '].[' + @database_name + '].sys.schemas as SCH with (nolock)
inner join[' + @server_name + '].[' + @database_name + '].sys.objects SOB with (nolock) on SOB.[schema_id] = SCH.[schema_id]
whereSOB.is_ms_shipped = 0
'
if @test_run = 1
begin
print @sql_string
end
else
begin
begin try
execute ( @sql_string )
end try
begin catch
select @activity = @activity + ' against server: [' + @server_name + ']'
goto log_error
end catch
end
select @row_count = count(*), @table_name = '#_mdm_objects' from #_mdm_objects
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'I'
,@row_count= @row_count
,@output_print= @output_print
begin try
select @activity = 'Create unique clustered index on #_mdm_objects'
create unique clustered index IX_#_mdm_objects
on #_mdm_objects (
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
)
end try
begin catch
goto log_error
end catch
/**************************************************************************************************
GET_OBJECT_DEFINITIONS
***************************************************************************************************/set @activity = 'EXEC ( @sql_string ): insert #_mdm_object_definitions'
set @sql_string = ''
set @sql_string = 'insert into #_mdm_object_definitions (
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
,colid
,[text]
) ' + char(13)
set @sql_string = @sql_string +
'
select
server_name = ''' + @server_name + '''
,database_name = ''' + @database_name + '''
,[schema_name] = SCH.[name]
,[object_name] = SOB.[name]
,SOB.type_desc
,SCO.colid
,SCO.[text]
from[' + @server_name + '].[' + @database_name + '].sys.schemas as SCH with (nolock)
inner join[' + @server_name + '].[' + @database_name + '].sys.objects SOB with (nolock) on SOB.[schema_id] = SCH.[schema_id]
inner join[' + @server_name + '].[' + @database_name + '].sys.syscomments SCO with (nolock) on SCO.id = SOB.[object_id]
whereSOB.is_ms_shipped = 0
'
if @test_run = 1
begin
print @sql_string
end
else
begin
begin try
execute ( @sql_string )
end try
begin catch
select @activity = @activity + ' against server: [' + @server_name + ']'
goto log_error
end catch
end
select @row_count = count(*), @table_name = '#_mdm_object_definitions' from #_mdm_object_definitions
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'I'
,@row_count= @row_count
,@output_print= @output_print
begin try
select @activity = 'Create unique clustered index on #_mdm_object_definitions'
create unique clustered index IX_#_mdm_object_definitions
on #_mdm_object_definitions (
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
,colid
)
end try
begin catch
goto log_error
end catch
/**************************************************************************************************
GET_TABLE_SCHEMA
***************************************************************************************************/set @activity = 'EXEC ( @sql_string ): insert #_mdm_monitor_table_schema'
set @sql_string = ''
set @sql_string = 'insert into #_mdm_monitor_table_schema (
server_name
,database_name
,table_schema
,table_name
,column_name
,ordinal_position
,column_default
,is_nullable
,data_type
,character_maximum_length
,character_octet_length
,numeric_precision
,numeric_precision_radix
,numeric_scale
,datetime_precision
) ' + char(13)
set @sql_string = @sql_string +
'
select
server_name = ''' + @server_name + '''
,database_name = ''' + @database_name + '''
,table_schema = cast( TABLE_SCHEMA as varchar(128) )
,table_name = cast( table_name as varchar(128) )
,column_name = cast( column_name as varchar(128) )
,ordinal_position
,column_default = cast( column_default as varchar(max))
,is_nullable
,data_type = cast( data_type as varchar(128) )
,character_maximum_length
,character_octet_length
,numeric_precision
,numeric_precision_radix
,numeric_scale
,datetime_precision
from[' + @server_name + '].[' + @database_name + '].information_schema.columns
union all
select
server_name = ''' + @server_name + '''
,database_name = ''' + @database_name + '''
,table_schema = cast( TABLE_SCHEMA as varchar(128) )
,table_name = cast( table_name as varchar(128) )
,column_name = cast( column_name as varchar(128) )
,ordinal_position
,column_default = cast( column_default as varchar(max))
,is_nullable
,data_type = cast( data_type as varchar(128) )
,character_maximum_length
,character_octet_length
,numeric_precision
,numeric_precision_radix
,numeric_scale
,datetime_precision
from[' + @server_name + '].[' + @database_name + '].information_schema.routine_columns
'
if @test_run = 1
begin
print @sql_string
end
else
begin
begin try
execute ( @sql_string )
end try
begin catch
select @activity = @activity + ' against server: [' + @server_name + ']'
goto log_error
end catch
end
select @row_count = count(*), @table_name = '#_mdm_monitor_table_schema' from #_mdm_monitor_table_schema
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'I'
,@row_count= @row_count
,@output_print= @output_print
begin try
select @activity = 'Create unique clustered index on #_mdm_monitor_table_schema'
create unique clustered index IX_#_mdm_monitor_table_schema
on #_mdm_monitor_table_schema (
server_name
,database_name
,table_schema
,table_name
,ordinal_position
,column_name
)
end try
begin catch
goto log_error
end catch
if @test_run = 1 goto test_run
/**************************************************************************************************
DELETE_OD_DELTAS
***************************************************************************************************/begin try
select @activity = 'Delete od deltas from [' + @server_name + '].[' + @database_name + ']'
deletedbo.t_monitor_od_deltas
where
server_name = @server_name
anddatabase_name = @database_name
select @row_count = @@rowcount, @table_name = 't_monitor_od_deltas'
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'D'
,@row_count= @row_count
,@output_print= @output_print
end try
begin catch
goto log_error
end catch
/**************************************************************************************************
DELETE_TS_DELTAS
***************************************************************************************************/begin try
select @activity = 'Delete ts deltas from [' + @server_name + '].[' + @database_name + ']'
deletedbo.t_monitor_ts_deltas
where
server_name = @server_name
anddatabase_name = @database_name
select @row_count = @@rowcount, @table_name = 't_monitor_ts_deltas'
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'D'
,@row_count= @row_count
,@output_print= @output_print
end try
begin catch
goto log_error
end catch
/**************************************************************************************************
DELETE_OBJECTS
***************************************************************************************************/-- select count(*) from sys.objects
begin try
select @activity = 'Delete objects from [' + @server_name + '].[' + @database_name + ']'
deletedbo.t_monitor_objects
where
is_ignored = 0x0
andserver_name = @server_name
anddatabase_name = @database_name
select @row_count = @@rowcount, @table_name = 't_monitor_objects'
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'D'
,@row_count= @row_count
,@output_print= @output_print
end try
begin catch
goto log_error
end catch
/**************************************************************************************************
INSERT_OBJECTS
***************************************************************************************************/begin try
select @activity = 'Insert objects into [' + @server_name + '].[' + @database_name + ']'
insert into dbo.t_monitor_objects(
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
)
select
OBJ.server_name
,OBJ.database_name
,OBJ.[schema_name]
,OBJ.[object_name]
,OBJ.type_desc
-- select *
from#_mdm_objects as OBJ
wherenot exists (
select*
fromdbo.t_monitor_objects as sub
wheresub.server_name= OBJ.server_name
andsub.database_name= OBJ.database_name
andsub.[schema_name]= OBJ.[schema_name]
andsub.[object_name]= OBJ.[object_name]
andsub.type_desc= OBJ.type_desc
)
select @row_count = @@rowcount, @table_name = 't_monitor_objects'
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'I'
,@row_count= @row_count
,@output_print= @output_print
end try
begin catch
goto log_error
end catch
/**************************************************************************************************
DELETE_OBJECT_MISSING_DEFINITIONS
***************************************************************************************************/begin try
--
-- select * from dbo.t_monitor_od_deltas
--
select @activity = 'Delete missing definitions from [' + @server_name + '].[' + @database_name + ']'
deleteOBD
fromdbo.t_monitor_object_definitions as OBD
where
OBD.server_name = @server_name
andOBD.database_name = @database_name
andnot exists (
select*
from#_mdm_object_definitions as OJD
where
OJD.server_name= OBD.server_name
andOJD.database_name= OBD.database_name
andOJD.[schema_name]= OBD.[schema_name]
andOJD.[object_name]= OBD.[object_name]
andOJD.type_desc= OBD.type_desc
andOJD.colid= OBD.colid
)
select @row_count = @@rowcount, @table_name = 't_monitor_object_definitions'
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'D'
,@row_count= @row_count
,@output_print= @output_print
end try
begin catch
goto log_error
end catch
/**************************************************************************************************
UPDATE_OBJECT_DEFINITION_TEXT_DELTAS
***************************************************************************************************/begin try
--
-- select * from dbo.t_monitor_od_deltas
--
select @activity = 'Update definition deltas in [' + @server_name + '].[' + @database_name + ']'
updateOBD
setOBD.[text] = OJD.[text]
,OBD.transaction_id = @transaction_id
,OBD.batch_date = current_timestamp
-- select *
from#_mdm_object_definitionsas OJD
inner joindbo.t_monitor_object_definitionsas OBD
on
OBD.server_name= OJD.server_name
andOBD.database_name= OJD.database_name
andOBD.[schema_name]= OJD.[schema_name]
andOBD.[object_name]= OJD.[object_name]
andOBD.type_desc= OJD.type_desc
andOBD.colid= OJD.colid
andOBD.[text]<> OJD.[text]
select @row_count = @@rowcount, @table_name = 't_monitor_object_definitions'
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'U'
,@row_count= @row_count
,@output_print= @output_print
end try
begin catch
goto log_error
end catch
/**************************************************************************************************
INSERT_NEW_OBJECT_DEFINITIONS
***************************************************************************************************/begin try
-- select * from dbo.t_monitor_object_definitions where
select @activity = 'Insert object definitions into [' + @server_name + '].[' + @database_name + ']'
insert into dbo.t_monitor_object_definitions(
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
,colid
,[text]
,transaction_id
)
select
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
,colid
,[text]
,transaction_id = @transaction_id
-- select *
from#_mdm_object_definitions as dub
wherenot exists (
select*
fromdbo.t_monitor_object_definitions as sub
wheresub.server_name= dub.server_name
andsub.database_name= dub.database_name
andsub.[schema_name]= dub.[schema_name]
andsub.[object_name]= dub.[object_name]
andsub.type_desc= dub.type_desc
andsub.colid= dub.colid
)
select @row_count = @@rowcount, @table_name = 't_monitor_object_definitions'
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'I'
,@row_count= @row_count
,@output_print= @output_print
end try
begin catch
goto log_error
end catch
/**************************************************************************************************
DELETE_MISSING_TABLE_SKHEMAS
***************************************************************************************************/-- select count(*) from sys.objects
begin try
select @activity = 'Delete missing schemas from [' + @server_name + '].[' + @database_name + ']'
-- declare @system_date datetime, @cr_lf char(2); select @system_date = getdate(),@cr_lf = char(13) + char(10)
deleteMTS
fromdbo.t_monitor_table_schemas as MTS
where
MTS.server_name = @server_name
andMTS.database_name = @database_name
andnot exists (
select*
from#_mdm_monitor_table_schema as sub
where
sub.server_name= MTS.server_name
andsub.database_name= MTS.database_name
andsub.table_schema= MTS.table_schema
andsub.table_name= MTS.table_name
andsub.ordinal_position= MTS.ordinal_position
)
select @row_count = @@rowcount, @table_name = 't_monitor_table_schemas'
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'D'
,@row_count= @row_count
,@output_print= @output_print
end try
begin catch
goto log_error
end catch
/**************************************************************************************************
UPDATE_CHANGED_TABLE_SKHEMAS
***************************************************************************************************/-- select count(*) from sys.objects
begin try
select @activity = 'Update changed schemas in [' + @server_name + '].[' + @database_name + ']'
-- declare @system_date datetime, @cr_lf char(2); select @system_date = getdate(),@cr_lf = char(13) + char(10)
updateMTS
setMTS.column_name= TSM.column_name
,MTS.column_default= TSM.column_default
,MTS.is_nullable= TSM.is_nullable
,MTS.data_type= TSM.data_type
,MTS.character_maximum_length= TSM.character_maximum_length
,MTS.character_octet_length= TSM.character_octet_length
,MTS.numeric_precision= TSM.numeric_precision
,MTS.numeric_precision_radix= TSM.numeric_precision_radix
,MTS.numeric_scale= TSM.numeric_scale
,MTS.datetime_precision= TSM.datetime_precision
,MTS.transaction_id= @transaction_id
,MTS.batch_date= current_timestamp
from#_mdm_monitor_table_schemaas TSM
inner joindbo.t_monitor_table_schemasas MTS
onMTS.server_name= TSM.server_name
andMTS.database_name= TSM.database_name
andMTS.table_schema= TSM.table_schema
andMTS.table_name= TSM.table_name
andMTS.ordinal_position= TSM.ordinal_position
andnot (
MTS.column_name= TSM.column_name
andMTS.is_nullable= TSM.is_nullable
andMTS.data_type= TSM.data_type
and ( MTS.column_default is null AND TSM.column_default is null
OR MTS.column_default = TSM.column_default
)
and ( MTS.character_maximum_length is null AND TSM.character_maximum_length is null
OR MTS.character_maximum_length = TSM.character_maximum_length
)
and ( MTS.character_octet_length is null AND TSM.character_octet_length is null
OR MTS.character_octet_length = TSM.character_octet_length
)
and ( MTS.numeric_precision is null AND TSM.numeric_precision is null
OR MTS.numeric_precision = TSM.numeric_precision
)
and ( MTS.numeric_precision_radix is null AND TSM.numeric_precision_radix is null
OR MTS.numeric_precision_radix = TSM.numeric_precision_radix
)
and ( MTS.numeric_scale is null AND TSM.numeric_scale is null
OR MTS.numeric_scale = TSM.numeric_scale
)
and ( MTS.datetime_precision is null AND TSM.datetime_precision is null
OR MTS.datetime_precision = TSM.datetime_precision
)
)
select @row_count = @@rowcount, @table_name = 't_monitor_table_schemas'
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'U'
,@row_count= @row_count
,@output_print= @output_print
end try
begin catch
goto log_error
end catch
/**************************************************************************************************
INSERT_NEW_TABLE_SKHEMAS
***************************************************************************************************/begin try
-- select top 10 * from t_monitor_table_schemas
select @activity = 'Insert table schema into [' + @server_name + '].[' + @database_name + ']'
insert into dbo.t_monitor_table_schemas(
server_name
,database_name
,table_schema
,table_name
,column_name
,ordinal_position
,column_default
,is_nullable
,data_type
,character_maximum_length
,character_octet_length
,numeric_precision
,numeric_precision_radix
,numeric_scale
,datetime_precision
,transaction_id
)
select
server_name
,database_name
,table_schema
,table_name
,column_name
,ordinal_position
,column_default
,is_nullable
,data_type
,character_maximum_length
,character_octet_length
,numeric_precision
,numeric_precision_radix
,numeric_scale
,datetime_precision
,transaction_id = @transaction_id
-- select *
from#_mdm_monitor_table_schema as TSM
wherenot exists (
select*
fromdbo.t_monitor_table_schemas as TAS
whereTAS.server_name= TSM.server_name
andTAS.database_name= TSM.database_name
andTAS.table_schema= TSM.table_schema
andTAS.table_name= TSM.table_name
andTAS.ordinal_position= TSM.ordinal_position
)
select @row_count = @@rowcount, @table_name = 't_monitor_table_schemas'
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'I'
,@row_count= @row_count
,@output_print= @output_print
end try
begin catch
goto log_error
end catch
test_run:
/**************************************************************************************************
THE_END
***************************************************************************************************/set @activity = 'END________' + upper( @procedure_name )
exec dbo.usp_util_activity_logging
@transaction_id = @transaction_id
,@procedure_name = @procedure_name
,@activity = @activity
,@output_print = @output_print
return @@error
/**************************************************************************************************
LOG_THE_ERROR_RAISE_THE_ERROR_AND_RETURN
***************************************************************************************************/log_error:
select @activity = 'Error: ' + @activity
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@activity= @activity
,@error_number= @error_number output
,@error_severity= @error_severity output
,@error_state= @error_state output
,@error_message= @error_message output
,@output_print= @output_print
select @error_message = @activity + '::' + @error_message
raiserror ( @error_message, @error_severity, @error_state ) with nowait
return @error_number
end -- [usp_monitor_db_merge]
GO
USE [UTILITY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TR_T_MONITOR_TABLE_SCHEMAS_INSERT]
on [dbo].[t_monitor_table_schemas]
for insert
/***************************************************************************************************************************************************
Purpose:Row level auditing of table schemas
Modified:
20090615 David Korb Created.
***************************************************************************************************************************************************//*_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_
_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_*/as
set nocount on
begin
declare @activity varchar(255)
declare @error_number int
declare @error_severity int
declare @error_state int
declare @error_message nvarchar(4000)
begin transaction monitor_audits
begin try
select @activity = 'Insert into t_monitor_table_schemas_audit'
insert into dbo.t_monitor_table_schemas_audit(
server_name
,database_name
,table_schema
,table_name
,column_name
,ordinal_position
,column_default
,is_nullable
,data_type
,character_maximum_length
,character_octet_length
,numeric_precision
,numeric_precision_radix
,numeric_scale
,datetime_precision
,transaction_id
,create_date
,batch_date
,audit_type
)
select
server_name
,database_name
,table_schema
,table_name
,column_name
,ordinal_position
,column_default
,is_nullable
,data_type
,character_maximum_length
,character_octet_length
,numeric_precision
,numeric_precision_radix
,numeric_scale
,datetime_precision
,transaction_id
,create_date
,batch_date
,audit_type = 'I'
frominserted
end try
begin catch
goto error_handling
end catch
commit transaction monitor_audits
goto skip_to_me_loo
error_handling:
select
@error_number= ERROR_NUMBER()
,@error_severity= ERROR_SEVERITY()
,@error_state= coalesce( nullif( ERROR_STATE(), 0 ), 1 )
,@error_message= @activity + ' failure; ' + ERROR_MESSAGE()
print '@error_number = ' + cast( @error_number as varchar )
print '@error_severity = ' + cast( @error_severity as varchar )
print '@error_state = ' + cast( @error_state as varchar )
print '@error_message = ' + @error_message
rollback transaction monitor_audits
raiserror ( @error_message, @error_severity, @error_state ) with nowait
skip_to_me_loo:
end -- [TR_T_MONITOR_TABLE_SCHEMAS_INSERT]
GO
USE [UTILITY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TR_T_MONITOR_TABLE_SCHEMAS_UPDATE]
on [dbo].[t_monitor_table_schemas]
for update
/***************************************************************************************************************************************************
Purpose:Row level auditing of table schemas
Modified:
20090615 David Korb Created.
***************************************************************************************************************************************************//*_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_
_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_*/as
set nocount on
begin
declare @activity varchar(255)
declare @error_number int
declare @error_severity int
declare @error_state int
declare @error_message nvarchar(4000)
declare @batch_date datetime
select @batch_date = current_timestamp
begin transaction monitor_audits
begin try
select @activity = 'Insert before audits'
insert into dbo.t_monitor_table_schemas_audit(
server_name
,database_name
,table_schema
,table_name
,column_name
,ordinal_position
,column_default
,is_nullable
,data_type
,character_maximum_length
,character_octet_length
,numeric_precision
,numeric_precision_radix
,numeric_scale
,datetime_precision
,transaction_id
,create_date
,batch_date
,audit_type
)
select
D.server_name
,D.database_name
,D.table_schema
,D.table_name
,D.column_name
,D.ordinal_position
,D.column_default
,D.is_nullable
,D.data_type
,D.character_maximum_length
,D.character_octet_length
,D.numeric_precision
,D.numeric_precision_radix
,D.numeric_scale
,D.datetime_precision
,D.transaction_id
,D.create_date
,D.batch_date
,audit_type = 'B'
fromdeleted as D
end try
begin catch
goto error_handling
end catch
begin try
select @activity = 'Insert after audits'
insert into dbo.t_monitor_table_schemas_audit(
server_name
,database_name
,table_schema
,table_name
,column_name
,ordinal_position
,column_default
,is_nullable
,data_type
,character_maximum_length
,character_octet_length
,numeric_precision
,numeric_precision_radix
,numeric_scale
,datetime_precision
,transaction_id
,create_date
,batch_date
,audit_type
)
select
I.server_name
,I.database_name
,I.table_schema
,I.table_name
,I.column_name
,I.ordinal_position
,I.column_default
,I.is_nullable
,I.data_type
,I.character_maximum_length
,I.character_octet_length
,I.numeric_precision
,I.numeric_precision_radix
,I.numeric_scale
,I.datetime_precision
,I.transaction_id
,I.create_date
,batch_date = @batch_date
,audit_type = 'A'
frominserted as I
end try
begin catch
goto error_handling
end catch
commit transaction monitor_audits
goto skip_to_me_loo
error_handling:
select
@error_number= ERROR_NUMBER()
,@error_severity= ERROR_SEVERITY()
,@error_state= coalesce( nullif( ERROR_STATE(), 0 ), 1 )
,@error_message= @activity + ' failure; ' + ERROR_MESSAGE()
print '@error_number = ' + cast( @error_number as varchar )
print '@error_severity = ' + cast( @error_severity as varchar )
print '@error_state = ' + cast( @error_state as varchar )
print '@error_message = ' + @error_message
rollback transaction monitor_audits
raiserror ( @error_message, @error_severity, @error_state ) with nowait
skip_to_me_loo:
end -- [TR_T_MONITOR_TABLE_SCHEMAS_UPDATE]
GO
USE [UTILITY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TR_T_MONITOR_TABLE_SCHEMAS_DELETE]
on [dbo].[t_monitor_table_schemas]
for delete
/***************************************************************************************************************************************************
Purpose:Row level auditing of table schemas
Modified:
20090615 David Korb Created.
***************************************************************************************************************************************************//*_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_
_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_*/as
set nocount on
begin
declare @activity varchar(255)
declare @error_number int
declare @error_severity int
declare @error_state int
declare @error_message nvarchar(4000)
declare @batch_date datetime
select @batch_date = current_timestamp
begin transaction monitor_audits
begin try
select @activity = 'Insert delete audits'
insert into dbo.t_monitor_table_schemas_audit(
server_name
,database_name
,table_schema
,table_name
,column_name
,ordinal_position
,column_default
,is_nullable
,data_type
,character_maximum_length
,character_octet_length
,numeric_precision
,numeric_precision_radix
,numeric_scale
,datetime_precision
,transaction_id
,create_date
,batch_date
,audit_type
)
select
server_name
,database_name
,table_schema
,table_name
,column_name
,ordinal_position
,column_default
,is_nullable
,data_type
,character_maximum_length
,character_octet_length
,numeric_precision
,numeric_precision_radix
,numeric_scale
,datetime_precision
,transaction_id
,create_date
,batch_date
,audit_type = 'D'
fromdeleted
end try
begin catch
goto error_handling
end catch
commit transaction monitor_audits
goto skip_to_me_loo
error_handling:
select
@error_number= ERROR_NUMBER()
,@error_severity= ERROR_SEVERITY()
,@error_state= coalesce( nullif( ERROR_STATE(), 0 ), 1 )
,@error_message= @activity + ' failure; ' + ERROR_MESSAGE()
print '@error_number = ' + cast( @error_number as varchar )
print '@error_severity = ' + cast( @error_severity as varchar )
print '@error_state = ' + cast( @error_state as varchar )
print '@error_message = ' + @error_message
rollback transaction monitor_audits
raiserror ( @error_message, @error_severity, @error_state ) with nowait
skip_to_me_loo:
end -- [TR_T_MONITOR_TABLE_SCHEMAS_DELETE]
GO
USE [UTILITY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TR_T_MONITOR_OBJECT_DEFINITIONS_INSERT]
on [dbo].[t_monitor_object_definitions]
for insert
/***************************************************************************************************************************************************
Purpose:Row level auditing of object definitions
Modified:
20090615 David Korb Created.
***************************************************************************************************************************************************//*_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_
_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_*/as
set nocount on
begin
declare @activity varchar(255)
declare @error_number int
declare @error_severity int
declare @error_state int
declare @error_message nvarchar(4000)
begin transaction monitor_audits
begin try
select @activity = 'Insert into t_monitor_object_definitions_audit'
insert into dbo.t_monitor_object_definitions_audit(
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
,colid
,[text]
,transaction_id
,create_date
,batch_date
,audit_type
)
select
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
,colid
,[text]
,transaction_id
,create_date
,batch_date
,audit_type = 'I'
frominserted
end try
begin catch
goto error_handling
end catch
commit transaction monitor_audits
goto skip_to_me_loo
error_handling:
select
@error_number= ERROR_NUMBER()
,@error_severity= ERROR_SEVERITY()
,@error_state= coalesce( nullif( ERROR_STATE(), 0 ), 1 )
,@error_message= @activity + ' failure; ' + ERROR_MESSAGE()
print '@error_number = ' + cast( @error_number as varchar )
print '@error_severity = ' + cast( @error_severity as varchar )
print '@error_state = ' + cast( @error_state as varchar )
print '@error_message = ' + @error_message
rollback transaction monitor_audits
raiserror ( @error_message, @error_severity, @error_state ) with nowait
skip_to_me_loo:
end -- [TR_T_MONITOR_OBJECT_DEFINITIONS_INSERT]
GO
USE [UTILITY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TR_T_MONITOR_OBJECT_DEFINITIONS_UPDATE]
on [dbo].[t_monitor_object_definitions]
for update
/***************************************************************************************************************************************************
Purpose:Row level auditing of object definitions
Modified:
20090615 David Korb Created.
***************************************************************************************************************************************************//*_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_
_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_*/as
set nocount on
begin
declare @activity varchar(255)
declare @error_number int
declare @error_severity int
declare @error_state int
declare @error_message nvarchar(4000)
declare @batch_date datetime
select @batch_date = current_timestamp
begin transaction monitor_audits
begin try
select @activity = 'Insert before audits'
insert into dbo.t_monitor_object_definitions_audit(
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
,colid
,[text]
,transaction_id
,create_date
,batch_date
,audit_type
)
select
D.server_name
,D.database_name
,D.[schema_name]
,D.[object_name]
,D.type_desc
,D.colid
,D.[text]
,D.transaction_id
,D.create_date
,D.batch_date
,audit_type = 'B'
fromdeleted as D
end try
begin catch
goto error_handling
end catch
begin try
select @activity = 'Insert after audits'
insert into dbo.t_monitor_object_definitions_audit(
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
,colid
,[text]
,transaction_id
,create_date
,batch_date
,audit_type
)
select
I.server_name
,I.database_name
,I.[schema_name]
,I.[object_name]
,I.type_desc
,I.colid
,I.[text]
,I.transaction_id
,I.create_date
,I.batch_date
,audit_type = 'A'
frominserted as I
end try
begin catch
goto error_handling
end catch
commit transaction monitor_audits
goto skip_to_me_loo
error_handling:
select
@error_number= ERROR_NUMBER()
,@error_severity= ERROR_SEVERITY()
,@error_state= coalesce( nullif( ERROR_STATE(), 0 ), 1 )
,@error_message= @activity + ' failure; ' + ERROR_MESSAGE()
print '@error_number = ' + cast( @error_number as varchar )
print '@error_severity = ' + cast( @error_severity as varchar )
print '@error_state = ' + cast( @error_state as varchar )
print '@error_message = ' + @error_message
rollback transaction monitor_audits
raiserror ( @error_message, @error_severity, @error_state ) with nowait
skip_to_me_loo:
end -- [TR_T_MONITOR_OBJECT_DEFINITIONS_UPDATE]
GO
USE [UTILITY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TR_T_MONITOR_OBJECT_DEFINITIONS_DELETE]
on [dbo].[t_monitor_object_definitions]
for delete
/***************************************************************************************************************************************************
Purpose:Row level auditing of object definitions
Modified:
20090615 David Korb Created.
***************************************************************************************************************************************************//*_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_
_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_*/as
set nocount on
begin
declare @activity varchar(255)
declare @error_number int
declare @error_severity int
declare @error_state int
declare @error_message nvarchar(4000)
declare @batch_date datetime
select @batch_date = current_timestamp
begin transaction monitor_audits
begin try
select @activity = 'Insert delete audits'
insert into dbo.t_monitor_object_definitions_audit(
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
,colid
,[text]
,transaction_id
,create_date
,batch_date
,audit_type
)
select
server_name
,database_name
,[schema_name]
,[object_name]
,type_desc
,colid
,[text]
,transaction_id
,create_date
,batch_date
,audit_type = 'D'
fromdeleted
end try
begin catch
goto error_handling
end catch
commit transaction monitor_audits
goto skip_to_me_loo
error_handling:
select
@error_number= ERROR_NUMBER()
,@error_severity= ERROR_SEVERITY()
,@error_state= coalesce( nullif( ERROR_STATE(), 0 ), 1 )
,@error_message= @activity + ' failure; ' + ERROR_MESSAGE()
print '@error_number = ' + cast( @error_number as varchar )
print '@error_severity = ' + cast( @error_severity as varchar )
print '@error_state = ' + cast( @error_state as varchar )
print '@error_message = ' + @error_message
rollback transaction monitor_audits
raiserror ( @error_message, @error_severity, @error_state ) with nowait
skip_to_me_loo:
end -- [TR_T_MONITOR_OBJECT_DEFINITIONS_DELETE]
GO
print char(13) + char(9) + db_name() + char(13) 
