Creating A Log Table To Track Changes To Database Objects In SQL Server

Tracking changes to database objects such as tables and stored procedures isn’t something SQL Server does natively.  Generally the only way to go back and see if a stored procedure was altered or dropped would be to dig through transaction logs which can be very tedious, even with the assistance of some 3rd party tools.  Even then, if you don’t quite know when the modification occurred, you could be looking for a virtual needle in the transaction log haystack.

Fortunately there is a solution to this problem.  Though this solution won’t be of any help if you’re currently digging through that haystack, but if you’re looking to begin tracking changes to system objects the steps outlined below will get you started.

The following steps will guide you through the process of creating the necessary database table and trigger to begin logging all CREATEALTER and DROP events that occur on tables, stored procedures and functions within a particular database.

You’ll want to deploy this solution on each individual database that you want to monitor.

To get started, log into SQL Management Studio and connect to the SQL Server containing the desired database.

The first step is to create the database table that will store the change log data.  Execute the following script against the desired database.

Make sure to enter the correct database name in the USE [DATABASE_NAME] line at the top of the script.

Once the table has been created you should see the following table structure if you navigate to the table within the server drop-down in Management Studio.  The table consist of 8 columns and 1 constraint.

The LogID column is the unique IDENTITY column and will automatically be populated each time a record is inserted into the table.

The remaining 7 columns will capture details of each database object modification being logged.  The column names are pretty self descriptive so I won’t go into any detail there.

The DF_EentsLog_EventDate constraint will insure that the current DATETIME is the only allowed value to be inserted into the EventDate column.

The next step is to create the database trigger that will be used to populate the ChangeLog table each time an applicable event occurs on the database.

The following script will create the trigger.

Again, make sure to enter the correct database name in the USE [DATABASE_NAME] line at the top of the script.

Once the trigger has been created you should see it listed under Database Triggers within the database in which it was created.

The script to create the trigger also includes the necessary code to enable the trigger so there is no need to perform this action.

At this point you should be all set.  Any CREATEALTER or DROP commands that are run against a TableStored ProcedureFunction or View within the database will be logged in the ChangeLog table going forward.

The easiest way to test this is to locate any stored procedure within the database, right-click the stored procedure and select Modify.  Once the procedure is displayed in the query window, make sure it is set to ALTER and then execute the stored procedure.

The following screenshot illustrates the data that was captured by the ALTER.

I have found this solution to be an invaluable tool in my DBA arsenal over the years.  Being able to quickly research when a particular action occurred has saved numerous hours of digging through transaction logs and countless headaches.  Whether you’re managing an expansive SQL Server environment or just a handful of databases, I highly recommend deploying the above solution.  Even if you don’t see an immediate need for it now, trust me, you’ll thank me later.