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)
