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…)