DDL Triggers: Logging DB objects changes in SQL Server 2005

We, SQL Server db coders, always lacked date modified filter option in management studio (or in enterprise manager). We always wanted that feature to be availabe in next Service Pack, or if not, in next version. But that never happened.

If you still don’t have solution to this – here are some good news: SQL Server 2005 comes with a new DDL triggers, using which we can track the changes in DB objects. And best thing about it is that it will take just few minutes to setup the log.

Here is how to do it:
1. First we create a log table, with necessary log fields as shown in figure 1.

2. Then we create a DDL-Trigger on the database (fig. 2) which gets triggered on any change to the database. We simply call EVENTDATA function, that returns a xml value that contains event details. We simply run a XQuery on the xml data to get necessary info, and save it to our log table.

Here is sample data in the table:

Also, if you are still stuck in some SQL Server 2000 database projects like me, you can still make use of the trigger by installing SQL Server 2005 and having your databases in 2000 mode (version 8).

In my team, we have created a simple DNN based UI, which allows us to list changes filtered by database, objects and date. Everybody simply creates an export file and puts it in svn at the end of the day. Then, all export files are combined in server database.