How To Track Database Changes

Problem Statement

While working on some development project, we, the developers very often face problems of keeping track of database object changes like new procedure, new tables, altered tables, new functions or views. If the change is in C# code, we have a way to do it by keeping a version on TFS but we can’t track the database version easily. In the end of the project, we often face problems to identify DB objects affected and generation the production migration script for those objects.

When I was working on a big project (enhancement project) dealing with major changes in SQL server, it really became very challenging for me to keep the track of DB changes and generating the production delta script. I believe, you as a developer face the same kind of problem as I do. However, I made the script given below to overcome such problems. I will go through the script and the plan of making it in detail in this article. First of all, let me tell you what you can actually do with this script.

1. You can have the current version of your database.
2. You can get a list last affected database objects.
3. You can easily generate the list of objects affected throughout the development phase.
4. It will make a developer’s life really easy.

Working principle

The script, which I have made is basically a stored procedure (named as: dbo.RunDbAudit). Also, I have kept it in a separate database called “Audit”). In the same SQL cluster, I have so many databases but for the sake of simplicity, I will hook up this script with two databases- Test and Test1.

My cluster will look, as shown below.

1. The top most Blue box shows the cluster.
2. Green box covers Audit database.
3. Red boxes show databases on which I want to hook up the audit script.
   
   
   Let’s assume at the above state  we have started development on Test. At the beginning of the development, I will run the script on Test to initiate the tracking of DB version.

Actually, at the backend, it created a new table(“__DB_Test”) in Audit database for this particular Test database.

The version will change only when any change is there.

To validate it, let’s alter spTest2 and re-run the audit script pointing to Test db.

The result is given below.


You can see that the change has been detected and the version 1.00 has been increased to 1.01 automatically. This is the beauty of this script. It has the features given below.

• Tracks new objects.
• Tracks modified objects.
• Tracks deleted objects.
• You can exclude any object by setting the value of the column AUDIT_EXCLUDED.

Hope, you enjoyed the article and this script will help you concentrate on the coding only.