Schema Comparisons using Visual Studio SQL Data Tools

Introduction

There are many occasions where we may need to compare the schema between two databases. For example, comparing Development and Production databases against each other, or comparing a database against a SQL Server Database project. Microsoft Visual Studio SQL Data Tools provides functionality that allows us to compare the structure of the database tables, stored procedures, views, and other database objects.

Before getting into this article, I would recommend to read the previous two article,

Create Branch in SQL Server Database Project

  • First lets create a new branch in the SQL Server Database Project, so that we can manage the scripts.
Fig.1 Create a new branch

Schema Compare feature in Visual Studio

  • From the main menu, go to the Tools -> SQL Server sub-menu -> New Schema Comparison
Fig.2 Choose New Shema Comparison
  • This will load the SqlSchemaCompare query window and the first thing that needs to be done is to select source and target from their corresponding drop-down lists
Fig.3 Select Source File
  • In the upper left corner of the SqlSchemaCompare query window, locate the Select source drop-down list and click on the Select Source item
Fig.4 Selecting the Source
  • The Select Source Schema dialog will be shown and in it, by default, the Database option is selected and under it, click the Select Connection button
Fig.5 Choose Database as Option
  • This will bring the new Connect dialog in which there are two tabs
    • History
    • Browse
Fig.6 Two tabs
  • After all information for SQL Server and database are entered, click the Connect button
Fig.7 Connect with SQL Server Database
  • Once all information is loaded, the Select Source Schema dialog will be shown again and here the OK button should be clicked in order to load the source data source
  • Now lets make the Target Project.
Fig.8 Choose Target File
Fig.9 Project as Target File
  • Select Target from the right upper of the screen or next to the source.
  • Since we are using SQL Server Database Project, choose the Project as option. By default, the current SQL Server Project will be shown in the dropdown.
  • Click Ok once the project is chosen.
Fig.10 Initial Look
  • This is how the initial look. Now we need to click the Compare in order to know the changes
Fig.11 Compare button option

Once the process of comparison is finished, the comparison results will be presented and there are two panels shown:

  1. The Results panel that contains all compared objects sorted by action for the update process
  2. The Object Definitions panel that shows the SQL scripts of compared objects from source and target SQL databases
Fig.12 Result window
  • Now that changes done table is listed in the Solution Explorer
Fig.13 Changes done file
  • We can save our Schema by simply applying CTRL + S and can be reused whenever it is required.
Fig.14 Save the Schema Tool
  • The below picture shows the saved schema compare tool.
Fig.15 Saved Location

Demo

Now lets see few examples, Lets create a new table in the SQL Server and apply the schema compare tool. 

CREATE TABLE Details.Exam
(
	ExamId INT PRIMARY KEY IDENTITY(1,1),
	ExamName NVARCHAR(50) NOT NULL
)
Fig.16 Example 1
  • If we run the schema compare tool, the newly added table will be displayed under Add folder as shown in the above. Then we need to apply the Update button, very next to the compare button.
Fig. 17 Newly added script
  • The newly created table is into the solution
  • Now lets see another example. Lets delete already existing table from the database. 
DROP TABLE [Details].[Marks]
Fig.18 Example 2
  • After running the Compare, the deleted table will be displayed in the delete folder. Now if we apply the update process, the table will be deleted from SQL Server Database Project in the Visual Studio.
Fig.19 Deleted Script
  • The table is removed from the SQL Server Database Project.
  • Now let’s change the procedure in the database. That changes done SP will be displayed under the Changes folder.
Fig.20 Example 3
Fig.21 Changes done files
  • Now we can commit the changes to the GitHub, since we have created GitHub repo for the SQL Server Project
Fig.22 Commit the changes and its files

Conclusion

In this article, we had a detailed look on the Schema Compare Tool in SQL Server Project. With this simple tool, we can manage the Scripts when working in a large number of team or when multiple developers works in database at the same time. I hope you all found this article much useful. Please share your feedback in the comment section.

Consider reading other SQL articles of Mine

5 thoughts on “Schema Comparisons using Visual Studio SQL Data Tools”

Add yours

  1. Pingback: Select Statement Execution Order In SQL Server – Learn With Sundar
  2. Pingback: Retrieve Unsaved SQL Query Scripts – Learn With Sundar
  3. Pingback: Aggregate Functions in SQL Server – Learn With Sundar
  4. Pingback: SQL Server String Functions – Learn With Sundar
  5. Pingback: SQL Server Date Functions – Learn With Sundar

Leave a Reply

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

Gravatar
WordPress.com Logo

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

Facebook photo

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

Cancel

Connecting to %s

Website Built with WordPress.com.

Up ↑

%d bloggers like this: