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 INTName 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