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:

<!--[endif]-->

And in VS2010:

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

<!--[endif]-->

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

<!--[endif]-->

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.

<!--[endif]-->

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

<!--[endif]-->

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.

<!--[endif]-->

Now, click on Compare:

After clicking it shows what it is doing for you:

And this is the result after comparison:

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:

<!--[endif]-->

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

<!--[endif]-->

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

<!--[endif]-->

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

<!--[endif]-->

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

<!--[endif]-->

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

<!--[endif]-->

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

<!--[endif]-->

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

<!--[endif]-->

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

<!--[endif]-->

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:

<!--[endif]-->

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!!!

<!--[endif]-->

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

<!--[endif]-->

Thanks for reading :)