SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Change Monitor in SQL Server 2005

By David Korb,

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.

Total article views: 1695 | Views in the last 30 days: 2
Related Articles

audit data change, before and updated value

any way to find before and after updated value on tables?


changes objects ?

Howto find all changed objects since a date


Primary Key creation - before and after bulk insert

Primary Key creation - before and after bulk insert


Index creation after of before populate?

is better to create the index after of before?


How to change object owner......

Change Object Owner