Easy way to compare SQL Server table schemas

Problem

Within the last 15+ years of work experience as an MSSQL DBA and Developer for small, medium and large corporations the simple task of keeping table schemas in sync amongst development, QA and production environments seems to be a recurring task. The market offers a variety of good tools that can help to synchronize SQL Server environments, however there is a quick and dirty way that can be used to compare table schemas directly from Microsoft SQL Server which we will cover in this tip.

Solution

Without the use of third party tools, one way to compare table schemas using native T-SQL is to query the INFORMATION_SCHEMA.COLUMNS metadata and build a custom made procedure or logic that compares two tables.

Another way to do this is to use the dynamic management function sys.dm_exec_describe_first_result_set, available since SQL Server 2012 that takes a T-SQL statement as input parameter and outputs the metadata description.

Let’s see how we can take advantage of this dynamic management function to build a way to compare table structures.

Create Sample Tables

Let’s create two tables as follows.  For simplicity I am creating these in the same database, but this could be done among different databases and even different instances of SQL Server and we could use Linked Servers and four part naming to address the objects if we want to compare against different servers.

-- create table in database
CREATE TABLE WebUsers (
usrID INT IDENTITY(1,1) not null,
usrFirstName varchar(20) not null,
usrLastName varchar(20) not null,
usrMiddleName char(1) null,
usrPhonNum varchar(20) not null,
usrWebAddr varchar(100) not null
)
GO
-- create second table in database
CREATE TABLE WebUsers2 (
usrID INT not null,
usrFirstName varchar(20) not null,
usrLastName varchar(20) not null,
usrMiddleName char(1) null,
usrPhonNum varchar(20) not null,
usrWebAddr varchar(100) not null
)
GO

Output from dm_exec_describe_first_result_set

Let’s get familiar with the dynamic management function to see what kind of metadata information it retrieves.  We will just pull back data for the usrID and usrPhonNum columns.

select * 
from sys.dm_exec_describe_first_result_set (N'SELECT usrID, usrPhonNum FROM dbo.WebUsers', NULL, 0)
GO

We can see the metadata that is returned.

Running Comparisons

Let’s now compare the tables between our two tables.

You can see below that I am pulling back various columns from the dynamic management function and doing an OUTER JOIN between the two tables. This will allow me to join the two tables based on the column name. 

SELECT DEV.name as DEV_ColumnName, 
PROD.name as PROD_ColumnName, 
DEV.is_nullable as DEV_is_nullable, 
PROD.is_nullable as PROD_is_nullable, 
DEV.system_type_name as DEV_Datatype, 
PROD.system_type_name as PROD_Datatype, 
DEV.is_identity_column as DEV_is_identity, 
PROD.is_identity_column as PROD_is_identity  
FROM sys.dm_exec_describe_first_result_set (N'SELECT * FROM dbo.WebUsers', NULL, 0) DEV 
FULL OUTER JOIN  sys.dm_exec_describe_first_result_set (N'SELECT * FROM dbo.WebUsers2', NULL, 0) PROD 
ON DEV.name = PROD.name 
GO

We can see a comparison between the two datasets. We can see these tables are almost identical, except that the usrID is not an identity on WebUsers2.

Make Changes and Compare Again

Let’s now change the schema of the WebUsers2 table by making column WebAddr nullable and by adding a new column named usrWebIP.  We will just drop and recreate the table as follows.

-- run in Production database
drop table dbo.WebUsers2
GO
CREATE TABLE WebUsers2 (
usrID INT not null,
usrFirstName varchar(20) not null,
usrLastName varchar(20) not null,
usrMiddleName char(1) null,
usrPhonNum varchar(20) not null,
usrWebAddr varchar(100) null,
usrWebIP varchar(100) null
)
GO

We can now see the differences between the two tables.

Make More Changes and Compare

Let’s change the schema of the WebUsers2 table again and re-run the compare script.

-- run in Production database
drop table dbo.WebUsers2
GO
CREATE TABLE WebUsers2 (
usrID BIGINT not null,
usrFirstName nvarchar(20) not null,
usrLastName char(20) not null,
usrMiddleName char(1) null,
usrPhonNum varchar(20) not null,
usrWebAddr int null
)
GO

We can see the differences highlighted in blue.

Conclusion

We have seen how we can utilize a simple dynamic management function to create something meaningful and useful to quickly compare table metadata. A possible use of such function is to create a scheduled job that will detect any metadata changes from different environments.

References

• Information Schema Usage INFORMATION_SCHEMA.COLUMNS
• sys.dm_exec_describe_first_result_set

Next Steps

• Try to use Information_Schema to create a table compare procedure.
• Check out these Information Schema Columns Tips
• Evaluate these SQL Server Comparison Tools
• Read this tip: Ways to compare and find differences for SQL Server tables and data.