Schema Compare Using Visual Studio

Consider a situation where we need to compare multiple databases and update one of those with the latest changes. This can be easily done using the Schema Comparison Utility available in Visual Studio 2013/2012/2010/2008/2005, almost all versions.

Comparing two schema is one of the biggest challenges a developer can encounter during a development cycle. I know of many third party software and free tools that are available. But long ago Visual Studio itself introduced the Schema comparison utility in Visual Studio 2005.

This utility helps everyone to do the comparison easily. The utility has been substantially improved and now, using a couple of simple clicks, we can compare schema. These comparisons can be done against two databases, two Visual Studio Database Projects, or a database against a Visual Studio Database Project. Long ago I wrote an article on who to create a Database project using Visual Studio, see:

Experiencing SQL Server 2008 Database Projects in Visual Studio 2010

We will now proceed to the step-by-step procedure to compare schema. Here I have two SQL Server Databases created named TestDB1 and TestDB2. Open Visual Studio 2012, then from the "SQL" menu click on "Schema Compare" followed by "New Schema Comparison...".



This will open a SQL Schema Compare window, where we need to select the Source and Target for comparison.



Let's start selecting a Source and Target.



Click on "Select Source" from the drop down. The "Select Source Schema" window will be opened. Select "Database" and click on "New Connection". From the Connection Properties window select your server name and database and test the connection and finally click on the "OK" button. In the same way select a Target Schema and click on the Compare icon on the top left corner in the following image.



If it's a Visual Studio Database Project then select that from the "Project" section. We can easily compare against two databases, two Visual Studio Database Projects, or a database against a Visual Studio Database Project.



After completion of the comparison a result set will be displayed with the difference, if any.



Here you will see the Source and Target differences listed. In the Target database the UserMaster table structure is different, also the Email column length is different. A new table FruitMaster, view and procedure is missing in the Target database. The pencil-like icon indicates "Change"; the green plus-like icon indicates "Add", like delete indicates "Delete" if any object was dropped from the Source. Click on any result row to see the "Object Definitions" in the other window below, that will list the scripts. Now click on the "Update" icon above near to "Compare". A confirmation box will appear, click on "Yes".



A new window named "Data Tools Operations" will be opened, that will show the update activity on the Target database. Once the update is completed successfully, then click on the "Compare" icon again to recheck for any differences.



We can check for more successful or failure information by clicking on the links "View Preview", "View Script" and "View Results".

Note:- Same way we can compare two database project's or database project against a database or the other way etc...

That's it, in this way we can do the database comparison.

Thank You.