Friday, November 12, 2010

Finding out what changed between two versions of a SQL Server Database

This is just a quick note showing how you can compare the structure of two databases  easily.

These queries will spell out what (and their types)

-- One way Select name,type from MyDataBase_dev.sys.objects EXCEPT Select name,type from MyDataBase.sys.objects -- reverse direction Select name,type from MyDataBase.sys.objects EXCEPT Select name,type from MyDataBase_dev.sys.objects

The next item to check is usually columns, which follows a similar pattern:


Select Table_Name,Column_name from MyDataBase.Information_Schema.Columns EXCEPT Select Table_Name,Column_name from MyDataBase_Dev.Information_Schema.Columns Select Table_Name,Column_name from MyDataBase_Dev.Information_Schema.Columns EXCEPT Select Table_Name,Column_name from MyDataBase.Information_Schema.Columns

Many developers do not know about the very useful EXCEPT operator which excludes from one SELECT all matches in another SELECT. You can do this for the rest of the system catalogs (just do not include columns that are expected to be different between databases…)

1 comment:

  1. Visual Studio Ultimate has a nice GUI feature to do comparison of schema and data too. :) It even updates Database schema projects to/from live databases.