A very common requirement which can be satisfied by various tools. Personally I like using Visual Studio 2017 Community Edition and I thought I would do a quick overview of it.

First thing, you can find the download from this link: https://www.visualstudio.com/downloads/  and once installed (making sure that you select SQL Server Data Tools)  go find Visual Studio 2017 and you will be presented with your start screen.

Now the context of this blog is all SQL Server related so obviously we need to establish a connection to one to carry out the schema comparison, there are couple of ways to do this but the easiest is via the menu bar view > SQL Server Object Explorer.

Quite simply right click on the SQL Server top node and add a connection as shown below.

To start the schema compare you can find it via this route (red arrows) Tools > SQL Server > New Schema Comparison.

Next step is to select the source and target for the comparisons. It is powerful because you can compare a source schema with a target schema to determine differences between them. You can then update the target schema to match the source. To update you can either do a direct update or generate a script.

Once you have populated the source and target hit the compare button. There are 3 areas, delete, change and an add section.

If you are interested in something specific just click it and the object definition will be provided below. For this example I wanted to see a change difference and you can see the difference highlighted, more specifically extra computed columns.

If there is an object that you want to see on either side of the comparison you can then click the UPDATE button if you desire. For the example below shows a difference within the EmailAddress table being a Non-clustered Index.

A kind prompt just to confirm.

Lets view the script and results. As you can see the non clustered index creation script.

A very good tool !

Filed under: Admin, SQL SERVER Tagged: Admin, Schema Compare, SQL server, Technology, Visual Studio