Tracking changes to database objects, such as tables and stored procedures, is not something SQL Server does natively. In general, the only way to go back and see if a stored procedure was modified or deleted would be to review the transaction logs, which can be very tedious, even with the help of some third-party tools. Even then, if you don’t know exactly when the change occurred, you could be looking for a virtual needle in the transaction log haystack.
Fortunately, there is a solution to this problem. Although this solution won’t help you at all if you’re currently investigating that haystack, but if you’re looking to start tracking changes to system objects, the steps below will help you get started.
The The following steps will walk you through the process of creating the necessary database table and trigger to begin logging all CREATE, ALTER, and DROP events that occur on tables, stored procedures, and functions within a database in
You will want to implement this solution on each individual database that you want to monitor.
To get started, log in to SQL Management Studio and connect to the SQL Server that contains the desired database.
The first step is to create the database table that will store the changelog data. Run the following script on the desired database. Be sure to enter the correct database name on 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 navigate to the table within the server dropdown menu in Management Studio. The table consists of 8 columns and 1 constraint.
The LogID column is the unique IDENTITY column and will auto-populate each time a record is inserted into the table.
The remaining 7 columns will capture details of each database object modification that is logged. The column names are pretty self-descriptive, so I won’t go into the details there.
The DF_EentsLog_EventDate constraint will ensure that the current DATETIME is the only value allowed 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 whenever an applicable event occurs in the database.
The following script create the trigger. Again, be sure to enter the correct database name on the USE [DATABASE_NAME] line at the top of the script.
Once the trigger is created, you should see it at Database triggers within the database it was created on.
The script to create the trigger also includes the code needed to enable the trigger, so it is not necessary perform this action.
At this point, you should be all set. Any CREATE, ALTER, or DROP command that is executed on a Table, Stored Procedure, Function, or View within the 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 captured by ALTER.
He I have found this solution to be an invaluable tool in my DBA arsenal over the years. Being able to quickly investigate when a particular action occurred has saved many hours of digging through transaction logs and countless headaches. Whether you’re managing a sprawling SQL Server environment or just a handful of databases, I recommend implementing the solution above. Even if you don’t see an immediate need now, trust me, you’ll thank me later.
.