Tracking Object (Table/Function/Stored Procedure etc.,) changes in SQL Server 2005

Deeraj
Posted by in Sql Server category on for Advance level | Views : 21647 red flag

DBAs often need to track the changes being made to the database objects such as tables, user-defined functions and stored procedures etc.,. Our team size was 10 and a couple of members were responsible for each of the modules. My requirement was to track the changes made to the stored procedures right from its creation. My earlier articles explained on how source control can be made available for new/existing stored procedures. If you have missed those articles please catch them up at, http://dotnetfunda.com/articles.
This article aims at providing the readers on how the changes being made to the database objects can be tracked with minimal effort. To get into the details, I would first give preference to the 'Summary Report' that can be generated on the fly by making use of 'SQL Server management Studio (Developer/Later editions).

Follow the steps below to generate a report on the schema changes using SQL Server Management Studio

1. Connect to the intended SQL Server.

2. After successful connection one should see a Summary Window on the right side of the Object Explorer.

3. Click on Reports button and select 'Schema Changes History' from the dropdown as shown in the below picture.



4. The above action will bring up the following screen. This however will consume noticeable amount of time to load the data from all the available databases. For the reasons of demonstration only a glimpse of it is being provided and is as follows.



5. The above diagram is self- explanatory. It reveals, which database was modified, what object was modified, The type of object (Stored procedure, index, table, etc.,) , The DDL (create,alter,drop) operation that made the change, the Time of change and the login that has made the change.

The downside to the above report is that it says ' so and so object was modified by so and so user'. But, it fails to provide the information on exactly 'what was modified'. So, this report is of little use to me. I am more interested on the lines, 'Who/what/when'. So this limitation can be overcome by making use DDL triggers. Any DB programmer is aware of basic DML triggers that fire upon insert/delete/update operations. However, DDL triggers are a new concept introduced in SQL Server 2005 that will be fired upon a DDL operation such as create, alter and drop.

Am going to create a DDL trigger at the Database level. However, these can also be developed at the SERVER level. The scope of this article is to define a trigger at the database level. Create the following DDL trigger in the database whose objects needs to be tracked for changes.

CREATE TRIGGER [Admin_Backup_Objects]

ON DATABASE
FOR create_procedure, alter_procedure, drop_procedure,
create_table, alter_table, drop_table,
create_function, alter_function, drop_function
AS

SET NOCOUNT ON

DECLARE @data XML
SET @data = EVENTDATA()

INSERT INTO dbo.AdministratorLog(databasename, eventtype,objectname, objecttype, sqlcommand, loginname)
VALUES(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), -- value is case-sensitive
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)


The above code creates a trigger at the database level. Forget not to use the 'Use <DatabaseName>' before creating the above trigger.

In the above trigger,

'create_procedure, alter_procedure, drop_procedure, create_table, alter_table, drop_table,
create_function, alter_function, drop_function' are the events that cause the trigger to fire.

EVENTDATA() - captures all the necessary information on the lines, what/who/when in the form of an XML string.

/EVENT_INSTANCE/DatabaseName – Returns the databasename on which the DDL operation was performed.
/EVENT_INSTANCE/EventType – Returns the DDL operation, Create/Alter/Drop
/EVENT_INSTANCE/ObjectName – Returns name of a table/stored procedure/index etc.,
/EVENT_INSTANCE/ObjectType – Returns object type. Ex. StoredProcedure, Table, Index etc.,
/EVENT_INSTANCE/TSQLCommand – Returns complete T-SQL command that made the modification.
/EVENT_INSTANCE/LoginName – Returns the loginname which made the modification.

I recommend using Integrated Windows Authentication for every user authenticating to SQL Server which provides a unique user identification unlike SQL Server Authentication which may be used by multiple users and this would be difficult to track the person who has made the change.

AdministratorLog – A user defined table created in the datbase to log the needful information when a DDL operation is performed.

@data.value – is an xpath query to query an XML string.

So, Its quite clear that a record is being inserted in the user defined table by capturing the EVENTDATA() information.

Sample output on a few DDL operations:



When compared to my earlier articles that demonstrate how source control can be made available on stored procedures, this article provides the complete solution on source control combined with the other two articles ((http://www.dotnetfunda.com/articles/article20.aspx and http://www.dotnetfunda.com/articles/article21.aspx ). The reasons are:

1. When we have enabled source control on stored procedures using SQL Server Management Studio. There may be a chance that user has changed the source code and pressed F5 which would save the object in the database. However, the user intentionally/unintentionally fails to save the modifications to the file. In this case the version in the database is different from the one that is available in the VSS.

2. After reading this article, one should be clear that even when the user fails to save the file to VSS, there is always a tracking going on in the backend.

Hope you enjoyed the articles, Happy Development and Safe Source control.

For further interesting articles, please visit, http://dotnetfunda.com/articles

Page copy protected against web site content infringement by Copyscape

About the Author

Deeraj
Full Name: Deeraj Chakravarthy
Member Level: Starter
Member Status: Member
Member Since: 10/29/2007 1:00:04 AM
Country: India


Qualification: BCA, MScIS, MDSE, PMI-ACP

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)