Tuesday, February 3, 2015

Compare columns in a table in two databases

Problem
I have two databases and a table that should be the same in both. I need to verify that these tables have the same columns

Solution
/*
Change TableName to your table
Change DatabaseA and DatabaseB to the actual database names
*/

DECLARE @TableName varchar(500) = 'TableName'

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM DatabaseA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = TableName
EXCEPT
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM DatabaseB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = TableName

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM DatabaseB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = TableName
EXCEPT
SELECT COLUMN_NAME, DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM DatabaseA.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = TableName

Example
DatabaseA.TableName has an extra column called "ThisIsDifferent"
The second query would show this column since it's in DatabaseA.TableName and not DatabaseB.TableName

No comments:

Post a Comment

There was an error in this gadget