Compare Database Schema .sql Files across environments ( using Devart – CodeCompare )

Background

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.

Software Options

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:

  1. WinMerge
  2. Devart.com

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.

 

Code Compare

Download

The free version is available here.

The current version is v4.1.

 

Install

Install the downloaded executable.

 

Compare

Select the Source and Target folder

Initial

SelectSourceAndTargetFolder-Before

Browse for Folder

SelectSourceAndTargetFolder-Browse

Post

SelectSourceAndTargetFolder-After

 

Initial Difference Screen

All of the discovered files are shown within their corresponding folder.

ScreenShot

Differences.20160313

Explanation:

Based on the default color coding, here is how fails are displayed

  1. Blue – Identical
  2. Differences
    1. White – Files exist on the both sides, but there are differences
    2. Pink – File exist in Source, but not in Destination

 

Customization

We will focus our customization on two windows and those are:

  1. Code Compare – File
  2. Code Compare – Folder – Miscellaneous

Here is what both screens look like:

Screens

Options – Code Compare – File

Options-CodeCompare-File

Options – Code Compare – Folder – Comparison

Options-Folders-Comparison

 

Options – Code Compare – Folder – Miscellaneous

Options-CodeCompare-Folder-Miscellaneous

Changes

Panel Item Choice
Text Editor
Display – Line Numbers Change from Unchecked to Checked
 Code Compare – File
 Ignore whitespaces Change from Unchecked to Checked
 Ignore line breaks Change from Unchecked to Checked
  Ignore case Change from Unchecked to Checked
 Code Compare – Folder – Comparison
Rules based comparison – Ignore case Change from Unchecked to Checked
Rules based comparison – Ignore whitespaces Change from Unchecked to Checked
Rules based comparison – Ignore line breaks Change from Unchecked to Checked
 Code Compare – Folder – Miscellaneous
Hide identical files Change from Unchecked to Checked

 

 

Explanation:

Making these changes will reduce clutter.

Menu

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.

HideIdenticalFiles

 

File Differences

Once we made the customization changes mentioned above, we reduced the noise quite a bit.

The files shown had actual content differences.

Sample Differences

Here are some of the differences discovered.

Differences

Products.SaveProductCertificateHistory.sql

One of the files cited is Product.SaveProductCertificateHistory.sql

Products.SaveProductCertificateHistory.20160307

Explanation

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.

 

Listening

As I always say being off Radio for so many years now and so this weekend while riding along was first time hearing this….

Sevyn Streeter – nEXt ft. Kid Ink

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s