SQL Server Schema Compare

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.

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.

sqlserver

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

NodeSQL

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

toolssql

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.

StartCompare

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

MainPage.JPG

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.

extraCols

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.

createNCI

A kind prompt just to confirm.

areyousure

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

mainNCI

output

A very good tool !

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2 thoughts on “SQL Server Schema Compare

  1. Pingback: Schema Comparison With Visual Studio – Curated SQL

  2. What did you believe in the early periods of your creativity or even in childhood? Not in terms of superheroes and grandfather of frost, but what gave you strength and inspiration?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s