SQL Server provides us with different solutions to replicate or archive a database table or tables to another database, or the same database with different names. As an SQL Server Developer or Database Administrator, you may face situations when you need to check that the data in these two tables are identical, and if, by mistake, the data is not replicated between these two tables, you need to synchronize the data between the tables. In addition, if you receive an error message, that breaks the data synchronization or replication process, due to schema differences between the source and destination tables, you need to find an easy and fast way to identify the schema differences, ALTER the tables to make the schema identical in both sides and resume the data synchronization process.
In other situations, you need an easy way to get the YES or NO answer, if the data and schema of two tables are identical or not. In this article, we will go through the different ways to compare the data and schema between two tables. The provided methods in this article will compare tables that are hosted in different databases, which is the more complicated scenario, and can be also easily used to compare the tables located in the same database with different names.
Before describing the different methods and tools that can be used to compare the tables data and schemas, we will prepare our demo environment by creating two new databases and create one table in each database, with one small data type difference between these two tables, as shown in the CREATE DATABASE and CREATE TABLE T-SQL statements below:
CREATE DATABASE TESTDB CREATE DATABASE TESTDB2 CREATE TABLE TESTDB.dbo.FirstComTable ( ID INT IDENTITY (1,1) PRIMARY KEY, FirstName VARCHAR (50), LastName VARCHAR (50), Address VARCHAR (500) ) GO CREATE TABLE TESTDB2.dbo.FirstComTable ( ID INT IDENTITY (1,1) PRIMARY KEY, FirstName VARCHAR (50), LastName VARCHAR (50), Address NVARCHAR (400) ) GO
After creating the databases and tables, we will fill the two tables with five identical rows, then insert another new record in the first table only, as shown in the INSERT INTO T-SQL statements below:
INSERT INTO TESTDB.dbo.FirstComTable VALUES ('AAA','BBB','CCC')
GO 5
INSERT INTO TESTDB2.dbo.FirstComTable VALUES ('AAA','BBB','CCC')
GO 5
INSERT INTO TESTDB.dbo.FirstComTable VALUES ('DDD','EEE','FFF')
GO
Now the testing environment is ready to start describing the data and schema comparison methods.
Compare Tables Data Using a LEFT JOIN
The LEFT JOIN T-SQL keyword is used to retrieve data from two tables, by returning all records from the left table and only the matched records from the right table and NULL values from the right table when there is no matching between the two tables.
For data comparison purposes, the LEFT JOIN keyword can be used to compare two tables, based on the common unique column such as the ID column in our case, as in the SELECT statement below:
SELECT * FROM TESTDB.dbo.FirstComTable F LEFT JOIN TESTDB2.dbo.FirstComTable S ON F.ID =S.ID
The previous query will return the common five rows existing in the two tables, in addition to the row that exists in the first table and missing from the second one, by showing NULL values at the right side of the result, as shown below:
You can easily derive from the previous result that, the sixth column that exists in the first table is missed from the second table. To synchronize the rows between the tables, you need to insert the new record to the second table manually. The LEFT JOIN method is helpful in verifying the new rows but will not help in the case of updating the columns values. If you change the Address column value of the 5th row, the LEFT JOIN method will not detect that change as shown clearly below:
Compare Tables Data Using EXCEPT Clause
The EXCEPT statement returns the rows from the first query (left query) that are not returned from the second query (right query). In other words, the EXCEPT statement will return the difference between two SELECT statements or tables, that helps us easily to compare the data in these tables.
The EXCEPT statement can be used to compare the data in the previously created tables, by taking the difference between the SELECT * query from the first table and the SELECT * query from the second table, using the T-SQL statements below:
SELECT * FROM TESTDB.dbo.FirstComTable F EXCEPT SELECT * FROM TESTDB2.dbo. FirstComTable S
The result of the previous query will be the row that is available in the first table and not available in the second one, as shown below:
Using the EXCEPT statement to compare two tables is better than LEFT JOIN statement in that, the updated records will be caught in the data differences result. Assume that we updated the Address of row number 5 in the second table, and checked the difference using EXCEPT statement again, you will see that the row number 5 will be returned with the differences result as shown below:
The only disadvantage of using the EXCEPT statement to compare the data in two tables is that you need to synchronize the data manually by writing an INSERT statement for the missing records in the second table. Take into consideration that the two tables that are compared are keyed tables to have the correct result, with a unique key used for comparison. If we remove the ID unique column from the SELECT statement in both EXCEPT statement sides, and list the rest of non-key columns, as in the statement below:
SELECT FirstName, LastName, Address FROM TESTDB.dbo. FirstComTable F EXCEPT SELECT FirstName, LastName, Address FROM TESTDB2.dbo. FirstComTable S
The result will show that only the new records are returned, and the updated ones will not be listed, as shown in the result below:
Compare Tables Data Using a UNION ALL … GROUP BY
The UNION ALL statement can be also used to compare the data in two tables, based on a unique key column. To use the UNION ALL statement to return the difference between two tables, you need to list the columns to compare in the SELECT statement and use these columns in the GROUP BY clause, as shown in the T-SQL query below:
SELECT DISTINCT *
FROM
(
SELECT * FROM
( SELECT * FROM TESTDB.dbo. FirstComTable
UNION ALL
SELECT * FROM TESTDB2.dbo. FirstComTable) Tbls
GROUP BY ID,FirstName, LastName, Address
HAVING COUNT(*)<2) Diff
And only the row that exists in the first table and missed from the second table will be returned as shown below:
The previous query will also work fine in the case of updating records but in a different way. It will return the newly inserted records in addition to the updated columns from both tables, as in the case of row number 5, shown below:
Compare Tables Data Using SQL Server Data Tools
SQL Server Data Tools, also known as SSDT, built over Microsoft Visual Studio can be easily used to compare the data in two tables with the same name, based on a unique key column, hosted in two different databases and synchronize the data in these tables, or generate a synchronization script to be used later.
From the opened SSDT window, click the Tools menu -> SQL Server list and choose the New Data Comparison option, as shown below:
In the displayed connection window, you can choose from the previously connected sessions, or fill the Connection Properties window with the SQL Server name, credentials and the database name, then click Connect, as shown below:
In the displayed New Data Comparison wizard, specify the source and target databases names and the comparison options used in the tables comparison process, then click Next, as shown below:
In the next window, specify the name of the table, that should be the same name in the source and target databases, that will be compared in both databases and click Finish, as below:
The displayed result will show you the number of records that are found in the source and missed from the target one, found in the target and missed from the source, the number of updated records with the same key and different columns values (Different Records) and finally the number of identical records found in both tables, as shown below:
Click the table name in the previous result, you will find a detailed view of these findings, as shown below:
You can use the same tool to generate a script to synchronize the source and target tables or update the target table directly with the missing or different changes, as below:
If you click on the Generate Script option, an INSERT statement with the missing column in the target table will be displayed, as shown below:
BEGIN TRANSACTION
BEGIN TRANSACTION SET IDENTITY_INSERT [dbo].[FirstComTable] ON INSERT INTO [dbo].[FirstComTable] ([ID], [FirstName], [LastName], [Address]) VALUES (6, N'DDD', N'EEE', N'FFF') SET IDENTITY_INSERT [dbo].[FirstComTable] OFF COMMIT TRANSACTION
Choosing the Update Target option will ask you first for your confirmation to perform the change, as in the message below:
After the synchronization, you will see that the data in the two tables will be identical, as shown below:
Compare Tables Data Using “dbForge Studio for SQL Server” Third-Party Tool
In the SQL Server world, you can find a large number of third-party tools that make the life of the database administrators and developers easy. One of these tools, that make the database administration tasks a piece of cake, is the dbForge Studio for SQL Server, that provides us with easy ways to perform the database administration and development tasks. This tool can also help us in comparing the data in the database tables and synchronize these tables.
From the Comparison menu, choose New Data Comparison option, as shown below:
From the New Data Comparison wizard, specify the source and target database, then click Next:
Choose the suitable options from the wide range of available mapping and comparison options and click Next:
Specify the name of the table or tables that will participate in the data comparison process. The wizard will display a warning message in case there are any schema differences between the source and target databases tables. Click Compare to proceed:
The final result will show you in detail, the data differences between the source and target tables, with the ability to click 
to synchronize the source and destination tables, as shown below:
Compare Tables Schema Using sys.columns
As mentioned at the beginning of this article, to replicate or archive a table, you need to make sure that the schema of the source and target tables is identical. SQL Server provides us with different ways to compare the schema of the tables in the same database or different databases. The first method is querying the sys.columns system catalog view, that returns one row for each column of an object that has a column, with the properties of each column.
To compare the schema of tables located in different databases, you need to provide the sys.columns with the table name under the current database, without being able to provide a table hosted in another database. To achieve that, we will query the sys.columns twice, save the result of each query in a temp table and finally compare the result of these two queries using the EXCEPT T-SQL command, as shown clearly below:
USE TESTDB SELECT name, system_type_id, user_type_id,max_length, precision,scale, is_nullable, is_identity INTO #DBSchema FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.FirstComTable') GO USE TestDB2 GO SELECT name, system_type_id, user_type_id,max_length, precision,scale, is_nullable, is_identity INTO #DB2Schema FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.FirstComTable '); GO SELECT * FROM #DBSchema EXCEPT SELECT * FROM #DB2Schema
The result will show us that, the Address column definition is different in these two tables, with no specific information about the exact difference, as shown below:
Compare Tables Schema Using INFORMATION_SCHEMA.COLUMNS
The INFORMATION_SCHEMA.COLUMNS system view can be also used to compare the schema of different tables, by providing the table name. Again, to compare two tables hosted in different databases, we will query the INFORMATION_SCHEMA.COLUMNS two times, keep the result of each query in a temp table and finally compare the result of these two queries using the EXCEPT T-SQL command, as shown clearly below:
USE TestDB SELECT COLUMN_NAME, IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION,NUMERIC_SCALE INTO #DBSchema FROM [INFORMATION_SCHEMA].[COLUMNS] SC1 WHERE SC1.TABLE_NAME='FirstComTable' GO USE TestDB2 SELECT COLUMN_NAME, IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION,NUMERIC_SCALE INTO #DB2Schema FROM [INFORMATION_SCHEMA].[COLUMNS] SC2 WHERE SC2.TABLE_NAME='FirstComTable' GO SELECT * FROM #DBSchema EXCEPT SELECT * FROM #DB2Schema
And the result will be somehow similar to the previous one, showing that the Address column definition is different in these two tables, with no specific information about the exact difference, as shown below:
Compare Tables Schema Using dm_exec_describe_first_result_set
The tables schemas can be also compared by querying the dm_exec_describe_first_result_set dynamic management function, that takes a Transact-SQL statement as a parameter and describes the metadata of the first result set for the statement.
To compare the schema of two tables, you need to join the dm_exec_describe_first_result_set DMF with itself, providing the SELECT statement from each table as a parameter, as in the T-SQL query below:
SELECT FT.name , ST.name , FT.system_type_name , ST.system_type_name , FT.max_length , ST.max_length , FT.precision , ST.precision , FT.scale , ST.scale , FT.is_nullable , ST.is_nullable , FT.is_identity_column , ST.is_identity_column FROM sys.dm_exec_describe_first_result_set (N'SELECT * FROM TestDB.DBO.FirstComTable', NULL, 0) FT LEFT OUTER JOIN sys.dm_exec_describe_first_result_set (N'SELECT * FROM TestDB2.DBO.FirstComTable', NULL, 0) ST ON FT.Name =ST.Name GO
The result will be clearer this time, as you can compare by eye, the difference between the two tables, that is the size and type of the Address column, as shown below:
Compare Tables Schema Using SQL Server Data Tools
SQL Server Data Tools can be also used to compare the schema of tables located in different databases. Under the Tools menu, choose New Schema Comparison option from the SQL Server options list, as shown below:
After providing the connection parameters, click on the Compare button:
The comparison result will show you, in specific, the schema difference between the two tables in the shape of CREATE TABLE T-SQL commands, shaded as in the snapshot below:
You can easily click 
to synchronize the table schema or click 
to script the change and perform it later, as shown below:
Compare Tables Schema Using dbForge Studio for SQL Server Third Party Tool
The dbForge Studio for SQL Server tool provides us with the ability to compare the schema of the different database tables. From the Comparison menu, choose the New Schema Comparison option, as below:
After specifying the connection properties of both the source and target databases, choose the suitable mapping option, from the available choices and click Next:
Choose the schemas that you will compare its object and click Next:
Specify the table or tables that will participate in the schema comparison process and click Compare, if you want to skip changing the default settings in the Object Filter window, as below:
The displayed comparison result will show you the difference between the two tables schema, by exactly highlighting the part of the data type that differs between the two columns, with the ability to specify what action should be done to synchronize the two tables, as shown below:
If you arrange to synchronize the schema of the two tables, click on the button and specify in the Schema Synchronization wizard if you manage to execute the change directly on the target table, or just script it to be used in the future, as below:
Useful Links:
- Set Operators – EXCEPT and INTERSECT (Transact-SQL)
- Set Operators – UNION (Transact-SQL)
- Download SQL Server Data Tools (SSDT)
- Compare and Synchronize Data in One or More Tables with Data in a Reference Database
- sys.dm_exec_describe_first_result_set (Transact-SQL)
- sys.columns (Transact-SQL)
- System Information Schema Views (Transact-SQL)
Useful tools:
dbForge Schema Compare for SQL Server – reliable tool that saves your time and effort when comparing and synchronizing databases on SQL Server.
dbForge Data Compare for SQL Server – powerful SQL comparison tool capable of working with big data.
