Introduction

In this article, we are going to look at one of the most useful features in Visual Studio that is often ignored or to be frank, many people don’t even know about its existence. The topic is about the hidden gem that comes with Visual Studio called “New Schema Comparison…” under "SQL Server" in "Tools" menu.

The main usage of this tool is when the developer works with multiple instances of the same database across different environments like Development, QA, Stage and Production, etc., and when there are frequent schema changes in database that need to be tracked.

Although there are many third party software and tools like SQL Compare from Redgate and Schema Compare from DevArt, they are premium products and many developers are looking for a free tool that can solve their basic needs.

Process to Follow

In this article, we are going to compare two instances of the same database, one is called as Source and the other one as Target. We are going to replicate or mock realtime schema changes to have better insight. I have included SQL scripts for both Source as well as Target databases.

Script for source database can be downloaded here
Script for target database can be downloaded here

After downloading and executing the script to creating two databases, open Visual Studio to create a compare schema definition. Go to “Tools” Menu and under “SQL Server”, select “New Schema Comparison…”

We see SqlSchemaCompare window where we need to select Source and Target databases...

Click on the first dropdown “Select Source…” to choose our source database "SchemaCompareSource".

This will bring “Select Source Schema” window:

Here, we have three options to choose Source Database from:

Project (Database project)
Database and
Data-tier Application File (.dacpac file)

We are going to select “Database” from the Schema options; hit “New Connection…” to connect to our source database.

Click on “Test Connection” and then click OK on "Select Source Schema" window. Follow the same process to select Target database schema "SchemaCompareTarget". Save the file onto your disk.

Note: The file extension .scmp suggests a schema compare file.

Looking at the Action Menu items

Compare (Shift+ Alt + C): This action is used to compare selected source and target database schemas
Update (Shift+ Alt + U): Action can be used to update target schema with the source schema
Generate Script (Shift+ Alt + G): Used to generate SQL script which can be executed on target database in SSMS
Option: Allows us to choose object types to compare and apply filters
Group Result: To group results based on Action, Schema or Type
Show Equal Objects: Toggle this option to view equal objects
Show actions not supported for update: Objects that don't support update
Previous (Shift+ Alt +,): Move to previous change
Next (Shift+ Alt +.): Move to next change

After selecting Source and Target connection, click the Compare (Shift+ Alt + C) button to start comparing both schemas. This can be shown as:

From the result view, we can see objects grouped into four categories:

Delete: Objects deleted from source database and exists in target
Change: Objects that exist in both source and target and are being modified
Add: Objects added to the source but don’t exist at the target
No Action: Objects that exist in both source and target and have no changes

To compare details about deleted objects (Table_1 in our case), from the image the table is missing at the source.

To compare details about added objects (Table_2 in our case), from the image the table is present at the source but is missing at the target.

To compare details about changed object (Table_3 in our case), from the image you can see there has been a change in column name as well as a new relation (constraint) added at the source.

Another example to look at table changes. Changes that are highlighted in color.

Another example looking at Stored Procedure changes. Changes are highlighted in color.

Looking at objects that need NO changes as the schema is the same at both source and target databases. If you do not see “No Action” category... just toggle “Show Equal Object” action and compare again.

If you just want to compare objects of type “Stored Procedures” only, then go to Options and from “Object Type” tab, uncheck all objects under “Application-Scoped”.

Now expand “Application-Scoped” to select only “Stored Procedure” as shown below:

Click OK and hit Compare again. This will only show changes related to stored procedures…

Note: You can select any object type like Views, Triggers, User Defined functions and Indexes, etc. There are many object types to choose from options..

This is one of the best tools from Visual Studio when dealing with database schema comparison across different environments. This tool will definitely make developers life easy.

I hope you learnt something new from this article. Rate this Article, Bookmark and help me improve this topic with any suggestion in the comments sections below.

History

24^th October, 2017: Initial version

Free SQL Server Schema Comparison Tool in Visual Studio 2013