Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Easy way to compare SQL Server table schemas


By:   |   Read Comments   |   Related Tips: More > T-SQL

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.

sys.dm_exec_describe_first_result_set result

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.

sys.dm_exec_describe_first_result_set result

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.

sys.dm_exec_describe_first_result_set result

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.

sys.dm_exec_describe_first_result_set result

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

Next Steps


Last Update:






About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools