SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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 !

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


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

All About SQL

I am a Senior DBA with interest in MS technology especially SQL Server and Azure. During 2015 I was mentored by Paul Randal – Data Platform (SQL Server) MVP and during 2016 I completed my SQLskills Immersion training on Internals and Performance Tuning. When I am not working I am in the gym burning calories.

Comments

Leave a comment on the original post [blobeater.blog, opens in a new window]

Loading comments...