Tracking SQL schema changes

2011/08/31

In – Roll your own lightweight SQL server source control – Ryan McCauley has provided a SQL sproc that can be used for MS SQL databases to poll for schema changes – with any changes stored in a central table in master db.

An interesting approach and worth taking a look at. It contrasts to one that I have outlined previously to achieve a similar outcome (see Automatically tracking database schema changes which uses DDL event triggers and stores changes in a table – dbo.EventLog – in each individual database).

Advantages to his approach include:

  • Simplicity. One sproc is installed in the master database and that’s it. It can then check all databases on the SQL server instance – useful in a ‘legacy’ situation especially if a large number of databases needs to be managed.
  • All changes are in one table making it easier to query/report on.

Disadvantages include:

  • It does not identify changes in all types of SQL objects. It uses sys.objects.object_definition to check for changes so objects such as SQL base tables are not included. Though it would be possible to extend the routine to include further checks on other system tables eg INFORMATION_SCHEMA.COLUMNS).
  • It polls for changes. There is a chance that a change could occur between polling periods and therefore not be identified (the chance of this depends on the polling interval used and the rate of schema change on the SQL server).

With many teams building their own way for tracking schema changes, it does beg the question, why this functionality isn’t included as standard in MS SQL?