Click here to Skip to main content
14,977,910 members
Articles / Database Development / SQL Server
Article
Posted 23 Feb 2016

Stats

21K views
637 downloads
16 bookmarked

Free SQL Server Schema Comparison Tool in Visual Studio 2013

Rate me:
Please Sign up or sign in to vote.
4.79/5 (16 votes)
23 Feb 2016CPOL4 min read
A look at the free SQL Server Schema Comparison Tool in VS2013
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. We are going to look at Free SQL Server Schema Comparison Tool in Visual Studio 2013.

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…

Image 1

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

Image 2

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

Image 3

This will bring “Select Source Schema” window:

Image 4

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

  1. Project (Database project)
  2. Database and
  3. 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.

Image 5

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

Image 6

  • 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:

Image 7

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.

Image 8

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.

Image 9

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.

Image 10

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

Image 11

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

Image 12

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.

Image 13

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”.

Image 14

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

Image 15

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

Image 16

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

  • 24th October, 2017: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sreekanth Mothukuru
Technical Lead
India India
No Biography provided

Comments and Discussions

 
QuestionCan this be automated? Pin
nikhilfpt26-Aug-18 21:59
Membernikhilfpt26-Aug-18 21:59 
GeneralGood job. Pin
Member 1041007624-Feb-16 14:37
MemberMember 1041007624-Feb-16 14:37 
GeneralRe: Good job. Pin
Sreekanth Mothukuru24-Feb-16 22:13
MemberSreekanth Mothukuru24-Feb-16 22:13 
PraiseWorks with SQL Server Express Pin
RickZeeland24-Feb-16 8:27
mveRickZeeland24-Feb-16 8:27 
GeneralRe: Works with SQL Server Express Pin
Sreekanth Mothukuru24-Feb-16 22:13
MemberSreekanth Mothukuru24-Feb-16 22:13 
AnswerRe: Works with SQL Server Express Pin
RickZeeland24-Feb-16 23:25
mveRickZeeland24-Feb-16 23:25 
GeneralRe: Works with SQL Server Express Pin
Sreekanth Mothukuru25-Feb-16 1:18
MemberSreekanth Mothukuru25-Feb-16 1:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.