Comparison of Two Database Schemas in SQL Server
Tags: sql, database, visualstudio, schema
Published: 2020-03-12 15:25:34

To synchronize the schemas of two databases, you can use both standard approaches like [Visual Studio](https://visualstudio.microsoft.com/) and [SSDT](https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver15) and third-party tools.

This is how you can do it in Visual Studio:

First of all, Create a SQL Server Database project:

![Creating a SQL Server Database project](https://dev-to-uploads.s3.amazonaws.com/i/xxyxmg88079nnfwo14c3.jpg)
*Fig.1 Creating a SQL Server Database project*

Then import the database:

![Navigating to the database import window](https://dev-to-uploads.s3.amazonaws.com/i/i2rzskauha40o6ikv4hi.jpg)
*Fig.2 Navigating to the database import window*

In the window that will be opened, configure the connection to the necessary database and press **Start** to initiate the import process:

![Configuring and starting the database import process](https://dev-to-uploads.s3.amazonaws.com/i/a4g8f75osbz1e1pqoki5.jpg)
*Fig.3 Configuring and starting the database import process*

After this, you will be able to see folders, subfolders, and definitions of database objects in the project:

![Project after import](https://dev-to-uploads.s3.amazonaws.com/i/ntdaak7v6jdgo91yyqmw.jpg)
*Fig.4 Project after import*

In the same way, you will need to create a project and perform import for the second database.

Now, to compare the database schemas, you just need to right-click any of the two projects and then click **Schema Compare**… in the context menu:

![Navigating to the database schema comparison window](https://dev-to-uploads.s3.amazonaws.com/i/xso1lx4t3iww3brqdo0p.jpg)
*Fig.5 Navigating to the database schema comparison window*

As a result, the database schema comparison window will be opened.
Here you need to select source and target projects and then press **Compare** to initiate the comparison process:

![Database schema comparison window](https://dev-to-uploads.s3.amazonaws.com/i/rkmfb3jxndct7ndh8nzv.jpg)
*Fig.6 Database schema comparison window*

You can also use other tools to compare database schemas, such as [dbForge Schema Compare](https://www.devart.com/dbforge/sql/schemacompare/) which integrates with [SSMS](https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15) and is included in the [SQL Tools](https://www.devart.com/dbforge/sql/sql-tools/) bundle:

![Comparing database schemas in SSMS](https://dev-to-uploads.s3.amazonaws.com/i/uvihwgu0bb96s2mg457p.jpg)
*Fig.7 Comparing database schemas in SSMS*

Here you can configure the source and target for schema comparison:

![Configuring the source and target for schema comparison](https://dev-to-uploads.s3.amazonaws.com/i/23bla5l44x652k4bq3wi.jpg)
*Fig.8 Configuring the source and target for schema comparison*

Table mapping can be configured in the corresponding tab:

![Selecting a table for mapping](https://dev-to-uploads.s3.amazonaws.com/i/mmbet3ixzyzgw6kfhcv6.jpg)
*Fig.9 Selecting a table for mapping*

![Configuring field mapping in the table](https://dev-to-uploads.s3.amazonaws.com/i/ka1lauks307ngjpgaihd.jpg)
*Fig.10 Configuring field mapping in the table*

“Columns details” displays column definition information from two tables: source database on the left and target database on the right.

This is particularly useful when column names are different in the source and the target.

After schemas are compared, you can select the necessary table and see the table definition differences in two databases:

![Table schema comparison between two databases](https://dev-to-uploads.s3.amazonaws.com/i/w7uj8s5pvhr5znklx1dg.jpg)
*Fig.11 Table schema comparison between two databases*

At the bottom left, the definition code of the source database table is displayed, and that of the target database is displayed at the bottom right.

If needed, a synchronization script for any schema table can be generated.