This article is half-done without your Comment! *** Please share your thoughts via Comment ***
For a database developer, comparing the table data and schema is a very common task. I already shared few versions of it.
Most of the time, developers are comparing schemas between Production Server and Development Server.
In this post, I am sharing a script to compare the table schema using dm_exec_describe_first_result_set.
Create sample tables:
|
1 2 |
CREATE TABLE tbl_A (ID INT, Name VARCHAR(100), Sub CHAR(5)) CREATE TABLE tbl_B (ID INT, Name VARCHAR(20), Sub VARCHAR(5), Grade INT) |
Get the schema differences:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT tbl_A.name as tbl_A_ColumnName ,tbl_B.name as tbl_B_ColumnName ,tbl_A.system_type_name as tbl_A_Datatype ,tbl_B.system_type_name as tbl_B_Datatype ,tbl_A.is_identity_column as tbl_A_is_identity ,tbl_B.is_identity_column as tbl_B_is_identity ,tbl_A.is_nullable as tbl_A_is_nullable ,tbl_B.is_nullable as tbl_B_is_nullable FROM sys.dm_exec_describe_first_result_set (N'SELECT * FROM tbl_A', NULL, 0) tbl_A FULL OUTER JOIN sys.dm_exec_describe_first_result_set (N'SELECT * FROM tbl_B', NULL, 0) tbl_B ON tbl_A.name = tbl_B.name GO |
Result:
Leave a Reply