Click here to Skip to main content
15,742,120 members
Articles / Database Development / SQL Server / SQL Server 2008
Posted 14 Jan 2011


25 bookmarked

SQL Server Schema Comparison Tool in Visual Studio 2010

Rate me:
Please Sign up or sign in to vote.
3.33/5 (8 votes)
14 Jan 2011CPOL3 min read
This article explains the new tool in Visual Studio 2010 which most of the users use to see the comparison report of the 2 different database schemas.


This article explains the new tool in Visual Studio 2010 which most of the users require for seeing the comparison report of the 2 different database schemas (say a development and a production db) in order to get the object that has been changed for the current releases.

In the market, there are many third party tools available to overcome this process, but in Visual Studio 2010, Microsoft has given the inbuilt functionality as a tool to compare the different schemas so that the changes can be easily compared and synced in different environments based on the object difference. Microsoft Database server version has provided with many features like database development, version controlling, unit testing, code analysis, code maintenance, etc…

Types of Comparison Options in Visual Studio 2010

Microsoft has provided different types of schema comparison in Visual Studio 2010. Users have given option to compare the below types:

  • SQL Server database – SQL Server 2008 or 2005 instance compare
  • Database project – a project which produces .dbschema file for SQL Server 2008 and 2005 instance
  • Server project - a project which produces .dbschema file for SQL Server 2008 and 2005 instance that contains definitions for server objects and objects in the 'master'
  • Data-tier application component (DAC) project - a project that produces a .dacpac file
  • .dbschema file - database or server project output
  • .dacpac file - data-tier application component (DAC) project output

Step by Step Process to Compare

Step 1 – Open Visual Studio 2010 by moving to Start > Program > Microsoft Visual Studio 2010 > Microsoft Visual Studio 2010.

Step 1

Step 2 – Once Visual Studio is opened, go to Data -> Schema Compare -> New Schema Comparison

Step 2

Step 3 – You will see a dialog box which requests for the source and destination db and the properties to connect to those db servers as below:

Step 3

Step 4 – Choose the source and the destination DB servers which you want to compare with or you can select the database file .dbschema to compare with the source and the destination. Later on, you can save these settings as .scmp file to use it later.

Step 5 – Once you are done with the selection of source and the destination comparison object, click on the OK button, it will take some time to get the comparison report based on the size of the data base and the objects to get compared.

Step 6 – Once the comparison is completed, the below screen will pop up which gets the result in a new list view where we can see the difference between the objects whether the object is same in both the environments, or change in the environments, etc. We can customize the view based on the requirement like, to see only the changed objects or same objects in both the schemas.

Step 6

Step 7 – You can see the detailed result of the object changed in the below window of the list view as shown in the below image:

Step 7

Step 8 – You can customize the schema comparison setting by going to the settings Data à Schema Compare a Schema Compare options.

Step 8

Step 9 – The options available for the schema comparison can be seen in the below image:

Step 9

Step 10 – We can update the schema at the later stage by exporting to the file and save it by selecting Export to Editor Window as below:

Step 10

Points of Interest

In the market, many third party tools are available to handle these tasks, but from the developer perspective, to maintain the credibility of the application, we can have a check day to day and get the objects impacted and keep them synced.


  • 14th January, 2011: Initial post


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

Comments and Discussions

GeneralMy vote of 1 Pin
Sting Z Cool1-Nov-12 7:29
Sting Z Cool1-Nov-12 7:29 
GeneralMy vote of 1 Pin
WestieBoy20-Feb-12 17:00
WestieBoy20-Feb-12 17:00 
QuestionDo this with code. Pin
Andre van der Merwe13-Sep-11 4:25
Andre van der Merwe13-Sep-11 4:25 
GeneralMy vote of 1 Pin
Paul E. Bible27-Jan-11 9:07
Paul E. Bible27-Jan-11 9:07 
Generalthanks for sharing - have 5 Pin
Pranay Rana24-Jan-11 2:03
professionalPranay Rana24-Jan-11 2:03 
GeneralNot in all versions Pin
Michael197316-Jan-11 10:46
Michael197316-Jan-11 10:46 
GeneralRe: Not in all versions Pin
KarthikAnbarasan16-Jan-11 14:46
KarthikAnbarasan16-Jan-11 14:46 
GeneralRe: Not in all versions Pin
JHDavis17-Jan-11 3:13
JHDavis17-Jan-11 3:13 
GeneralRe: Not in all versions Pin
ajbutler26-Jun-12 6:17
ajbutler26-Jun-12 6:17 
GeneralGood one Pin
Shahriar Iqbal Chowdhury/Galib14-Jan-11 21:27
professionalShahriar Iqbal Chowdhury/Galib14-Jan-11 21:27 
GeneralRe: Good one Pin
KarthikAnbarasan14-Jan-11 21:44
KarthikAnbarasan14-Jan-11 21:44 

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.