Database Schema Tracking

Posted: August 31, 2010 in SQLServerPedia Syndication

I am not sure why I have never done this before. I can think of many reasons why one would want to track the changes that are being made to their databases. There are the obvious reasons to do this:

  • Auditing (Make sure the changes that were supposed to happen really did)
  • Security (Who is making changes to your database)
  • Trouble Shooting (What has changed before the database performance took a dive)

In the script below (I have to admit I have only been using for a day or two so there may be some things I need to work out with it) I am inserting changes that are made to Tables, Procedures, Triggers, Functions into a table in a database I use for a management database. The script not only grabs the change, but who made the change, where they were when they made the change, and when the change was made. If I make changes to this I will update this post. If you have any suggestions please feel free to let me know. The trigger can be altered catch other DDL Events.

— This set of Steps will allow you to

— Track all changes made to the SCHEMA of a database

— Notify off of those changes

— Steps to implament


— 1) Create Dest Table

— 2) Create DDL Trigger

— 3) Create Procedure that looks at the last 24 hours for a change

— 4) Create Job that Executes Procedure in Step 3

–Step 1

DROP
TABLE schema_tracking

GO

CREATE
TABLE schema_tracking

(

ChangeDate DATETIME
NOT
NULL,

ChangeUser VARCHAR (20)
NOT
NULL,

ChangeHost VARCHAR(40)
NOT
NULL,

ObjectDatabase NVARCHAR(25)
NOT
NULL,

ObjectChanged NVARCHAR(1250)
NOT
NULL

)

GO

–Step 2

— Insert this into each database and including the model

Alter
TRIGGER Audit

ON
DATABASE

FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE, CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,

CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION

AS

INSERT
INTO DBStats.dbo.Schema_tracking VALUES(

GETDATE(),

SYSTEM_USER,

Host_name(),

DB_NAME(),

EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,‘nvarchar(max)’)

)

;


Comments
  1. Mark Inmon says:

    Chris, to take this to the next level how can you apply this to the 200+ databases on a SQL server.

    • Chris Shaw says:

      Good Question, and there are a couple changes that I already want to make to this. This has to sit in the database that you want to monitor so you are going to have to add this to each of the databases. I am not sure how we can get around that at this point. The item that I want to change is to not collect the comandText but the object name. I will work on making that change and update this.

      Chris

  2. Mark Inmon says:

    yes I saw comandText after the fact and got bitten by it

  3. Mark Inmon says:

    Chris would this work?

    @EventData.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘NVARCHAR(255)’),

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s