Automatic Data Collection of Database Schema Changes in MS SQL Server

Introduction

Have you ever faced a situation when you need to make changes in a stored procedure or a view very quickly? I have, very often, especially at the implementation stage. Unfortunately, a version control system cannot help in this case. Still, how could I understand that something has been modified, and when?

This article describes a possible solution for automatic data collection about database schema changes in MS SQL Server. As usual, I will be glad to hear any alternative solutions.

Solution

  1. Create two tables: the first one will be for each database, the second one – for all databases:
  2. Create a DDL-trigger for a database which collects scheme changes:

I recommend adjusting a filter and not making a DDL-trigger for the whole server. It is useless, as you will get a lot of needless information. In this case, it is better to create a trigger for each database.
However, you will have to turn off this trigger during complicated operations, for example, replication. But later, you will be able to turn it on again.

  1.  You will need to gather information in a single table. For example, you can do it with a task in the SQL Server Agent one time a week.
  2. It is possible to gather everything in one table by another way you prefer.

Additionally, I recommend deleting old data.

Result

In this article, I have analyzed an example of implementing an automatic data collection about changes of databases schemes in MS SQL Server. It allows us to find out what and when has been modified, and, if necessary, to revert them. In general, this solution may be helpful at the implementation stage where there are a lot of mistakes and when we have different versions of databases copies to be analyzed. If you wish to find out a reason for changes, you can do it by retrieving a revision history.

Also read:

Automatic Data Collection about Completed Tasks in MS SQL Server

Evgeniy Gribkov

Evgeniy Gribkov

Evgeniy is a MS SQL Server database analyst, developer and administrator. He is involved in development and testing of tools for SQL Server database management. Evgeniy also writes SQL Server-related articles.
Evgeniy Gribkov

Evgeniy Gribkov

Evgeniy is a MS SQL Server database analyst, developer and administrator. He is involved in development and testing of tools for SQL Server database management. Evgeniy also writes SQL Server-related articles.