.NET Zone is brought to you in partnership with:

I'm a UK based BI Consultant, specialising in SQL Server, C# and Business Intelligence. I've been working with SQL Server for over 10 years, and have over 15 years in development. Nick is a DZone MVB and is not an employee of DZone and has posted 23 posts at DZone. You can read more from them at their website. View Full User Profile

Visual Studio 2012 Schema Comparison

08.20.2012
| 12902 views |
  • submit to reddit

Having recently been playing with the newly released Visual Studio 2012, one of the really nice features that I’ve seen is the Database Schema Comparison functionality.

If you’d like to follow along with this, you’ll need the ContosoBI database, which is available here.

This can be seen by launching VS2012, choosing New Project, and selecting the SQL Server Database Project. Don’t forget to give the Project a name, I called mine dbSchemaComparison.

image

When the Solution has been created, you’ll be presented with the Solution explorer.

image

In here, you’ll want to right-click on the Project name, and choose Import > Database. In here, you’ll need to create a new connection to your database. Also, if you are wanting to track everything, you need to check the Permissions and Database Settings tick boxes. Then click Start.

image

While the process is running, you’ll be presented with a dialog box showing the progress. When it’s completed, click Finish.

image

Now, when you look in the Solution Explorer, you’ll see a set of SQL Scripts that have been created to match the structure in the database.

image

My next step was to connect to the database using SQL Server Management Studio, and alter one of the tables. I decided to add an Index to the DimAccount table. The index was called ix_date, and I added the LoadDate field from the DimAccount table to it.

image

The final step in this process is to go back into Visual Studio, right click on the Project and choose Schema Compare. When this window opens, you have two drop down boxes. The left contains the Project that you have in VS2012; the right will need to be populated with a database for comparison.

image

When you’ve populated the database on the right, click the Compare button. The Schemas from the two projects are loaded and compared. The results are then displayed on the screen. As can be seen below, it’s pretty obvious what the differences between the environments are.

image

If you then want to sync the environments, you need to remember that you need to move the changes from Source (Left) to Target (Right). If you want to remove them from the Right (database), then you can click Update (or the script button next to it, to generate a script). Alternatively, if you want to update your project, you can click the ‘switch’ button between the two projects and rerun the Compare.

A really nice feature, I think you’ll agree.

Published at DZone with permission of Nick Haslam, author and DZone MVB.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)