Share via

T-SQL: How to Compare Two Tables Definition / Metadata in Different SQL Databases

  • Article

This article is about the example on T-SQL Scripts to compare two tables definition / metadata in different databases.

The T-SQL Script  [used to compare two tables definition / metadata in different databases] in this article can be used from SQL Server 2012 and above versions because the function uses sys.dm_exec_describe_first_result_set  that was introduced in SQL Server 2012.

Create sample databases:  

IF EXISTS (SELECT name  FROM master.sys.databases WHERE name  = N'SQLServer2012')  
BEGIN 
DROP DATABASE  SQLServer2012  
END 
CREATE DATABASE  SQLServer2012  
   
   
   
IF EXISTS (SELECT name  FROM master.sys.databases WHERE name  = N'SQLServer2014')  
BEGIN 
DROP DATABASE  SQLServer2014  
END 
CREATE DATABASE  SQLServer2014

Create sample tables in above created databases:

USE SQLServer2012  
GO  
CREATE Table  Test1 (Id INT  NOT NULL  Primary Key,Name VARCHAR(100))  
   
USE SQLServer2014  
GO  
CREATE Table  Test2 (Id INT, Name VARCHAR(100), Details XML)

Below T-SQL Script can be used to compare two tables definition / metadata in different databases:

USE SQLServer2012  
GO  
SELECT A.name DB1_ColumnName,  
B.name DB2_ColumnName,  
A.is_nullable DB1_is_nullable,  
B.is_nullable DB2_is_nullable,  
A.system_type_name DB1_Datatype,  
B.system_type_name DB2_Datatype,  
A.collation_name DB1_collation,  
B.collation_name DB2_collation,  
A.is_identity_column DB1_is_identity,  
B.is_identity_column DB2_is_identity,  
A.is_updateable DB1_is_updateable,  
B.is_updateable DB2_is_updateable,  
A.is_part_of_unique_key DB1_part_of_unique_key,  
B.is_part_of_unique_key DB2_part_of_unique_key,  
A.is_computed_column DB1_is_computed_column,  
B.is_computed_column DB2_is_computed_column,  
A.is_xml_document DB1_is_xml_document,  
B.is_xml_document DB2_is_xml_document       
FROM SQLServer2012.sys.dm_exec_describe_first_result_set (N'SELECT * FROM Test1', NULL, 0) A  
FULL OUTER JOIN   SQLServer2014.sys.dm_exec_describe_first_result_set (N'SELECT * FROM Test2', NULL, 0) B   
ON A.name = B.name 

 

See Also