We need to compare database schema files across our environments.
In earlier posts, we went over how to use SQL Server Data Tools ( SSDT ) to compare live environments.
In this post, we will go over one of the many text compare tools that is available to compare SQL Schema files.
Quick Definition of “Schema” files
By the term Schema files we mean Tables, Views, Stored Procedures and other reverse engineered objects.
We generated a copy of the live database objects using ScriptDB.
Expectedly there are quite a few tools that target the text file compare market. In our case, we do not need a tool that simply compares two files. We need one that compares at a more comprehensive level, folders and hopefully the sub-directories.
Available Options includes:
While WinMerge is a good tool, we found CodeCompare to be more far more polished as the interface allows quick access to often asked questions.
The free version is available here.
The current version is v4.1.
Install the downloaded executable.
Select the Source and Target folder
Browse for Folder
Initial Difference Screen
All of the discovered files are shown within their corresponding folder.
Based on the default color coding, here is how fails are displayed
- Blue – Identical
- White – Files exist on the both sides, but there are differences
- Pink – File exist in Source, but not in Destination
We will focus our customization on two windows and those are:
- Code Compare – File
- Code Compare – Folder – Miscellaneous
Here is what both screens look like:
Options – Code Compare – File
Options – Code Compare – Folder – Comparison
Options – Code Compare – Folder – Miscellaneous
Making these changes will reduce clutter.
Changes \ Hide identical Files
To “stick” the “Hide Identical Files” option on the current window, please access the menu and check same on the Changes\”Hide Identical Files” window.
Once we made the customization changes mentioned above, we reduced the noise quite a bit.
The files shown had actual content differences.
Here are some of the differences discovered.
One of the files cited is Product.SaveProductCertificateHistory.sql
The difference is that on the left side we have a left join Products.ProductDocumentFields; wherease on the right we have an inner join on same table.
As I always say being off Radio for so many years now and so this weekend while riding along was first time hearing this….