This article is about the example on T-SQL Script 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 version because I have used the function sys.dm_exec_describe_first_result_set that was introduced from SQL Server 2012 .
IF EXISTS (
SELECT
name
FROM
master.sys.databases
WHERE
= N
'SQLServer2012'
)
BEGIN
DROP
DATABASE
SQLServer2012
END
CREATE
'SQLServer2014'
SQLServer2014
Create sample tables in above created databases :
USE SQLServer2012
GO
Table
Test1 (Id
INT
NOT
NULL
Primary
Key
,
Name
VARCHAR
(100))
USE SQLServer2014
Test2 (Id
(100), Details XML)
A.
DB1_ColumnName,
B.
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
SQLServer2012.sys.dm_exec_describe_first_result_set (N
'SELECT * FROM Test1'
, 0) A
FULL
OUTER
JOIN
SQLServer2014.sys.dm_exec_describe_first_result_set (N
'SELECT * FROM Test2'
, 0) B
ON
= B.
Naomi N edited Original. Comment: Interesting article, may need a bit more explanations