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

from	dbo.t_monitor_objects
where	is_ignored = 0x0
and	is_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

from	dbo.t_monitor_objects as MOB
inner join	dbo.t_monitor_object_definitions as OBD
			on
				OBD.server_name		= MOB.server_name
			and	OBD.database_name	= MOB.database_name
			and	OBD.[schema_name]	= MOB.[schema_name]
			and	OBD.[object_name]	= MOB.[object_name]
			and	OBD.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

from	dbo.t_monitor_objects as MOB
inner join	dbo.t_monitor_od_deltas as ODD
			on
				ODD.server_name		= MOB.server_name
			and	ODD.database_name	= MOB.database_name
			and	ODD.[schema_name]	= MOB.[schema_name]
			and	ODD.[object_name]	= MOB.[object_name]
			and	ODD.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


from	dbo.v_monitor_object_definitions as MOB
left join	dbo.v_monitor_object_definition_deltas as ODD
			on
				ODD.server_name		= MOB.server_name
			and	ODD.database_name	= MOB.database_name
			and	ODD.[schema_name]	= MOB.[schema_name]
			and	ODD.[object_name]	= MOB.[object_name]
			and	ODD.type_desc		= MOB.type_desc
			and	ODD.colid		= MOB.colid
where	MOB.is_ignored = 0x0
and	exists (
		select	sub.server_name
		from	dbo.v_monitor_object_definition_deltas as sub
		where
			sub.server_name		= MOB.server_name
		and	sub.database_name	= MOB.database_name
		and	sub.[schema_name]	= MOB.[schema_name]
		and	sub.[object_name]	= MOB.[object_name]
		and	sub.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

from	dbo.t_monitor_dimensions	as DAD
inner join	dbo.t_monitor_facts	as DAF on DAF.dimension = DAD.dimension
where	DAD.is_active = 0x1
and	DAF.is_active = 0x1
and	exists (
		select	sub.database_name
		from	dbo.t_database_list as sub
		where	sub.database_name = DAF.database_name
		and	sub.is_active = 0x1
	)
and	exists (
		select	sub.server_name
		from	dbo.t_server_list as sub
		where	sub.server_name = DAF.server_name
		and	sub.is_active = 0x1
	)
and	DAD.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

from	dbo.t_monitor_dimensions	as DAD
inner join	dbo.t_monitor_facts	as DAF on DAF.dimension = DAD.dimension
where	DAD.is_active = 0x1
and	DAF.is_active = 0x1
and	exists (
		select	sub.database_name
		from	dbo.t_database_list as sub
		where	sub.database_name = DAF.database_name
		and	sub.is_active = 0x1
	)
and	exists (
		select	sub.server_name
		from	dbo.t_server_list as sub
		where	sub.server_name = DAF.server_name
		and	sub.is_active = 0x1
	)
and	DAD.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

from	dbo.v_monitor_list			as MOL
left join	dbo.v_monitor_distribution_list	as 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	*
from	dbo.t_monitor_table_schemas_audit
where	table_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 )

	from	dbo.t_monitor_table_schemas_audit as AUD
	where	audit_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

from	dbo.t_monitor_table_schemas_audit as AUD
where	exists (
		select	sub.server_name
		from	LATEST_VERSION as sub
		where	sub.server_name		= AUD.server_name
		and	sub.database_name	= AUD.database_name
		and	sub.table_schema	= AUD.table_schema
		and	sub.table_name		= AUD.table_name
		and	sub.ordinal_position	= AUD.ordinal_position
		and	sub.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

from	dbo.t_monitor_ts_deltas as MTS
where	exists (
		select	sub.server_name
		from	dbo.v_monitor_object_deltas as sub
		where	sub.server_name		= MTS.server_name
		and	sub.database_name	= MTS.database_name
		and	sub.[schema_name]	= MTS.table_schema
		and	sub.[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

from	dbo.t_monitor_table_schemas as MTS
where	exists (
		select	sub.server_name
		from	dbo.v_monitor_table_schema_after as sub
		where	sub.server_name		= MTS.server_name
		and	sub.database_name	= MTS.database_name
		and	sub.table_schema	= MTS.table_schema
		and	sub.table_name		= MTS.table_name
		and	sub.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

	from	dbo.t_monitor_object_definitions_audit
	where
		audit_id in (
			select	max( sub.audit_id )
			from	dbo.t_monitor_object_definitions_audit as sub
			where
				sub.audit_type not in ( 'B', 'D' )
			and	sub.server_name = coalesce( @server_name, sub.server_name )
			and	sub.database_name = coalesce( @database_name, sub.database_name )
			and	sub.[schema_name] = coalesce( @schema_name, sub.[schema_name] )
			and	sub.[object_name] = @object_name
			and	sub.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

	from	dbo.t_monitor_table_schemas_audit
	where
		audit_id in (
			select	max( sub.audit_id )
			from	dbo.t_monitor_table_schemas_audit as sub
			where
				sub.audit_type not in ( 'B', 'D' )
			and	sub.server_name = coalesce( @server_name, sub.server_name )
			and	sub.database_name = coalesce( @database_name, sub.database_name )
			and	sub.table_schema = coalesce( @table_schema, sub.table_schema )
			and	sub.table_name = @table_name
			and	sub.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 *
	from	dbo.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
		where	id = @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]
	where	SOB.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
		where	id = @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]
	where	SOB.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
		where	id = @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)
		update	DOB
		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 *
		from	dbo.t_monitor_objects as DOB
		where	not exists (
				select	*
				from	#_ums_objects	as sub
				where
					sub.server_name  = DOB.server_name
				and	sub.database_name = DOB.database_name
				and	sub.[schema_name] = DOB.[schema_name]
				and	sub.[object_name] = DOB.[object_name]
				and	sub.type_desc = DOB.type_desc
			)
		and	DOB.is_active = 0x1
		and	DOB.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)
		update	DOB
		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 *
		from	dbo.t_monitor_objects		as DOB
		inner join	#_ums_objects	as dub on dub.server_name  = DOB.server_name
								and	dub.database_name = DOB.database_name
								and	dub.[schema_name] = DOB.[schema_name]
								and	dub.[object_name] = DOB.[object_name]
								and	dub.type_desc = DOB.type_desc
		where	DOB.is_active = 0x0
		and	DOB.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
		where	not exists (
				select	*
				from	dbo.t_monitor_objects as sub
				where	sub.server_name		= OBJ.server_name
				and	sub.database_name	= OBJ.database_name
				and	sub.[schema_name]	= OBJ.[schema_name]
				and	sub.[object_name]	= OBJ.[object_name]
				and	sub.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
		where	not exists (
				select	*
				from	dbo.t_monitor_od_deltas as sub
				where
					sub.server_name		= TOD.server_name
				and	sub.database_name	= TOD.database_name
				and	sub.[schema_name]	= TOD.[schema_name]
				and	sub.[object_name]	= TOD.[object_name]
				and	sub.type_desc		= TOD.type_desc
				and	sub.colid		= TOD.colid
			)
		and	not exists (
				select	*
				from	dbo.t_monitor_object_definitions as OBD
				where
					OBD.server_name		= TOD.server_name
				and	OBD.database_name	= TOD.database_name
				and	OBD.[schema_name]	= TOD.[schema_name]
				and	OBD.[object_name]	= TOD.[object_name]
				and	OBD.type_desc		= TOD.type_desc
				and	OBD.colid		= TOD.colid
				and	OBD.[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	*
				from	dbo.t_monitor_ts_deltas as sub
				where
					sub.server_name			= TTS.server_name
				and	sub.database_name		= TTS.database_name
				and	sub.table_schema		= TTS.table_schema
				and	sub.table_name			= TTS.table_name
				and	sub.ordinal_position		= TTS.ordinal_position
				and	sub.column_name			= TTS.column_name
			)
		and	not exists (
				select	*
				from	dbo.t_monitor_table_schemas as MTS
				where	MTS.server_name		= TTS.server_name
				and	MTS.database_name	= TTS.database_name
				and	MTS.table_schema	= TTS.table_schema
				and	MTS.table_name		= TTS.table_name
				and	MTS.ordinal_position	= TTS.ordinal_position
				and	MTS.column_name		= TTS.column_name
				and	MTS.is_nullable		= TTS.is_nullable
				and	MTS.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)
		update	MOB
		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 *
		from	dbo.t_monitor_objects as MOB
		where	MOB.is_ignored = 0x0
		and	MOB.is_od_delta = 0x0
		and	exists (
				select	*
				from	dbo.t_monitor_od_deltas	 as ODD
				where	ODD.server_name		= MOB.server_name
				and	ODD.database_name	= MOB.database_name
				and	ODD.[schema_name]	= MOB.[schema_name]
				and	ODD.[object_name]	= MOB.[object_name]
				and	ODD.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)
		update	MOB
		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 *
		from	dbo.t_monitor_objects as MOB
		where	MOB.is_ignored = 0x0
		and	MOB.is_ts_delta = 0x0
		and	exists (
				select	*
				from	dbo.t_monitor_ts_deltas as TSD
				where	TSD.server_name		= MOB.server_name
				and	TSD.database_name	= MOB.database_name
				and	TSD.table_schema	= MOB.[schema_name]
				and	TSD.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	*
		from	dbo.v_monitor_configurations
		where	server_name = @server_name
		and	database_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]
where	SOB.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]
where	SOB.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 + ']'
		delete	dbo.t_monitor_od_deltas
		where
			server_name = @server_name
		and	database_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 + ']'
		delete	dbo.t_monitor_ts_deltas
		where
			server_name = @server_name
		and	database_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 + ']'
		delete	dbo.t_monitor_objects
		where
			is_ignored = 0x0
		and	server_name = @server_name
		and	database_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
		where	not exists (
				select	*
				from	dbo.t_monitor_objects as sub
				where	sub.server_name		= OBJ.server_name
				and	sub.database_name	= OBJ.database_name
				and	sub.[schema_name]	= OBJ.[schema_name]
				and	sub.[object_name]	= OBJ.[object_name]
				and	sub.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 + ']'
		delete	OBD
		from	dbo.t_monitor_object_definitions as OBD
		where
			OBD.server_name = @server_name
		and	OBD.database_name = @database_name
		and	not exists (
				select	*
				from	#_mdm_object_definitions as OJD
				where	
					OJD.server_name		= OBD.server_name
				and	OJD.database_name	= OBD.database_name
				and	OJD.[schema_name]	= OBD.[schema_name]
				and	OJD.[object_name]	= OBD.[object_name]
				and	OJD.type_desc		= OBD.type_desc
				and	OJD.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 + ']'
		update	OBD
		set	OBD.[text] = OJD.[text]
			,OBD.transaction_id = @transaction_id
			,OBD.batch_date = current_timestamp

			-- select *
		from	#_mdm_object_definitions			as OJD
		inner join	dbo.t_monitor_object_definitions	as OBD
		on	
			OBD.server_name		= OJD.server_name
		and	OBD.database_name	= OJD.database_name
		and	OBD.[schema_name]	= OJD.[schema_name]
		and	OBD.[object_name]	= OJD.[object_name]
		and	OBD.type_desc		= OJD.type_desc
		and	OBD.colid		= OJD.colid
		and	OBD.[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
		where	not exists (
				select	*
				from	dbo.t_monitor_object_definitions as sub
				where	sub.server_name		= dub.server_name
				and	sub.database_name	= dub.database_name
				and	sub.[schema_name]	= dub.[schema_name]
				and	sub.[object_name]	= dub.[object_name]
				and	sub.type_desc		= dub.type_desc
				and	sub.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)
		delete	MTS
		from	dbo.t_monitor_table_schemas as MTS
		where
			MTS.server_name = @server_name
		and	MTS.database_name = @database_name
		and	not exists (
				select	*
				from	#_mdm_monitor_table_schema as sub
				where
					sub.server_name		= MTS.server_name
				and	sub.database_name	= MTS.database_name
				and	sub.table_schema	= MTS.table_schema
				and	sub.table_name		= MTS.table_name
				and	sub.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)
		update	MTS
		set	MTS.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_schema		as TSM
		inner join	dbo.t_monitor_table_schemas	as MTS
		on	MTS.server_name		= TSM.server_name
		and	MTS.database_name	= TSM.database_name
		and	MTS.table_schema	= TSM.table_schema
		and	MTS.table_name		= TSM.table_name
		and	MTS.ordinal_position	= TSM.ordinal_position
		and	not (
					MTS.column_name		= TSM.column_name
				and	MTS.is_nullable		= TSM.is_nullable
				and	MTS.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
		where	not exists (
				select	*
				from	dbo.t_monitor_table_schemas as TAS
				where	TAS.server_name		= TSM.server_name
				and	TAS.database_name	= TSM.database_name
				and	TAS.table_schema	= TSM.table_schema
				and	TAS.table_name		= TSM.table_name
				and	TAS.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'

		from	inserted
	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'

		from	deleted 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'

		from	inserted 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'

		from	deleted
	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'

		from	inserted
	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'

		from	deleted 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'

		from	inserted 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'

		from	deleted
	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)

Rate

3.83 (6)

Share

Share

Rate

3.83 (6)