We can use sp_help to display all table schema
sp_help import_OAN_clients
Will get below result:
But how we can compare two tables schema quickly via T-SQL? We can use INFORMATION_SCHEMA.COLUMNS to compare two tables schema easily, but this way cannot compare table cross database or instance. In this blog I will show how to compare it with internal function sys.dm_exec_describe_first_result_set
Create Demo Tables
CREATE TABLE demo1(
[User_ID] [nvarchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](100) NULL,
[BirthDate] datetime NULL,
[Citizenship] [nvarchar](100) NULL,
[Country] [nvarchar](20) NULL
)
CREATE TABLE demo2(
[User_ID] [nvarchar](50) NULL,
[FirstName] [nvarchar](100) NULL,
[LastName] [nvarchar](100) NULL,
[BirthDate] [nvarchar](30) NULL,
[Citizenship] [nvarchar](100) NULL,
[Country] [nvarchar](100) NULL
) How sys.dm_exec_describe_first_result_set is working
If you want to know all detail specs of sys.dm_exec_describe_first_result_set, you can go to microsoft developer community
before we start compare two tables, let’s see what kind of information the function can get
select * from sys.dm_exec_describe_first_result_set ( N’SELECT * FROM dbo.demo1′, NULL, 1)
We can see the returned schema as below:
Doing Comparisons
To do two tables comparison, we just need to simply doing OUTER JOIN between the two function results. (To do comparisons between to database or instance, please use linked servers and full part name to address the table. like product.dbo.demo2)
select a.column_ordinal,b.column_ordinal,a.name,b.name ,a.is_nullable,b.is_nullable ,a.system_type_name,b.system_type_name,a.max_length,b.max_length,a.precision,b.precision,a.scale,b.scale
from sys.dm_exec_describe_first_result_set ( N’SELECT * FROM dbo.demo1′, NULL, 1) as a
full outer join sys.dm_exec_describe_first_result_set ( N’select * from dbo.demo2′, NULL, 1) as b
on a.name = b.name
We can see the comparison result between to tables now.
