Schema and Data Compare Scripts in MS SQL

Hello All,

I was searching for a tool which gives me the Schema and or data comparison for SQL Server, its really needed while we are updating staging or updating our live site. There may be lots of tool available on net but I found one tool which is part of Microsoft® Visual Studio Team System 2008 Database Edition GDR R2.

Team Edition provides lots of functionality, mainly we are using to write Test Case which is very useful for Test Driven Development.

On top of this if you have Database edition then you can do lots of things with it. One of the feature is Schema and Data Compare. This blog post will walk you thru steps to compare data and schema.

Schema Comparison

1. Go to New Schema Comparison form Data menu

2. Next select the two database, one is source and other is target

 

3. When press OK will give you the Schema Compare of all the objects including Tables, Views, Stored Procedures and many more.

Here you can see the list of Objects and the Object Definitions windows

4. In following image Table is expanded so you can see the changes in details on what table what action is held.

 

5. To see more in details select one of the updated row and you can see the definition in Object Definitions window; it will generate Create script and highlight the difference. There is also Schema Update Script which will have alter script of all the changes that is detected during Schema Compare.

6. You can either copy the script or export to file or editor by using Export To Edition command on tool box.

This was all about how to detect schema change, its very handy with lots of options.

Data Comparison

Now same way we have Data Comparison, which is use to compare data, it provides tons of details and script for insert/update and delete. The steps are pretty much starlight forward. I have listed the steps here.

1. Click on New Data Comparison from Data menu

2. It will ask for Source and Target tables

3. On click of Finish, it will provide you all the information that is differ in between two database tables

You can see here it shows 2 rows are only in source, means two rows are added to SB_mst_Group table. It also provides the what exact rows are added in middle section along with other options.

In third last section which is Data Update Script; provides you the script for the action, here we have addition, so its provide the insert statements which we can run in UAT to get the date.

I found it very useful and easy to generate required script in few mouse click!