Visual Studio 2012 SQL Schema Comparison

Visual Studio 2012 is very much a developer friendly and robust IDE. Earlier in Visual Studios 2008/2010, you may have encountered the problem that while comparing schemas if you forget to specify the exact version of SQL, but then VS2012 is intelligent enough to figure it out. We can say that in earlier versions, it was not a problem but required extra care from developers.

This article covers the enhanced schema comparison tool in terms of friendliness using self explanatory pictures/images. In 2005/8 versions of Visual Studio, you will not easily find this comparison option. Though, comparison options up to Visual Studio 2010 can be found in the Data menu, as in:


And in VS2010:

2010 schema compare option.png

But VS2012 has moved this option from the Data menu to the SQL menu as in the following:

Image 3.jpg

Visual Studio 2010's schema comparison for selecting source and target database looks as in the following:

Image 4.jpg

But, it has a very beautiful selection UI in VS2012 as below. This time the dialogue box for selecting the database comes after selecting the "Select Source" or "Select Target" option from the corresponding dropdowns. These dropdowns are under the tabbed UI that is automatically opened for this comparison job.

Note: I am comparing schemas without adding any project/solution.


Image 5.jpg

You get other options like selecting databases etc. after selecting "Select source" from the dropdown.

new connection.png

As displayed in the above image, I am choosing the database then clicking on "New Connection…". After clicking on New Connection, we can select the data source and database. Here I am selecting the ArticleDemoDB database as the source. Once the database is selected we return to the same (above) dialogue and this time the Edit Connection button is enabled. Now we will say OK to this dialogue and follow the same steps to select a target database. For the target database I have selected BlogDB. These two are different databases. My BlogDB has only 1 table and I will use this target database to be updated with my source database i.e. ArticleDemoDB. The ArticleDemoDB database has 3 tables in it. There is much that can be done with this wonderful comparison tool but I will just show you the enhanced look & feel/features of Visual Studio with 2012 version instead of going deeper.

source and target schema is selected.png

Now, click on Compare:

compare tool button.png

After clicking it shows what it is doing for you:

loading schema from source and target.png

And this is the result after comparison:

first result after comparison.png

In the above image, you can see that the target database has a table that is not present in the source database. The first one item is already selected and thus the object definition for the left side pane (source) is empty and the target pane has a table creation code. If I select an item that is not present in the target database but in the source then the screen will be like this:

first result after comparison2.png

VS2012 has schema compare options and it can be found on clicking the options toolbutton:

schema compare options.png

And after clicking on this options tool button, The schema compare options selections screen is shown as in the following:

schema compare options choice.png

Let's generate a script to see the script functionality by clicking on the "Generate Script" tool-button.

generate script toolbutton.png

This will generate a script and show the result screen as in the following:

generated script.png

Now we will see its execution plan by clicking on the tool "Display Estimated Execution Plan" as in the following:

execution plan button.png

On clicking it shows the execution plan as in the following:

execution plan.png

Well, now we will update the target database from the source database by clicking "Update" as:

update schema toolbutton.png

It confirms the update; let's say "OK" to the confirmation dialogue box.

update schema confirmation dialog.png

After confirmation, it shows the update progress status while updating in the status bar. And when it is finished, the status is as in the following:

schema update complete status.png

Let's see the effect of the update. We will check it from Microsoft SQL Server Management Studio for our database BlogDB, and it is showing all 3 new tables!!!

update effect.png

All three tables from the source database have been copied into this target database.

Note: This update deletes the existing objects of the target table. In our example we had a table inside BlogDB that is not available after this update.

Now we will once again click on Compare to see the comparison result from this tool, and it shows as in the following image with status "comparison complete. No differences detected.".

final comparison result.png

Thanks for reading :) 

Similar Articles