Compare two databases using T-SQL

: Joel Lipman; Transact-SQL; 24 September 2013; Hits: 7913

Applies to:

MS SQL Server Management Studio 2008
Two Transact-SQL databases

What?
A quick article on how to compare two Microsoft databases using the tools provided with SQL Server and without having to download any third-party products.

Why?
I googled and binged and all I could find were people selling third party products... they don't get it. If you are reading this, it's likely you've paid for a commercial version of a Microsoft product which cannot possibly be cheap. MS SQL Server Management Studio (SSMS) comes included so use it...

How?
Well I started with the method #1 listed here and then just built on this.

Method #1
I got this first query from the awesome site that is StackOverflow:

copy raw

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Run this command against both databases and you have their full schemas. Use a comparison tool to compare the outputs.

Method #2
Enhancing the above which does a little more of the work, requires in the following query for you to replace the terms "myDB1" with the first database name and "myDB2" with the name of the second database to compare to:

copy raw

-- columns in old database but not in new
SELECT t1.* FROM [myDB1].[INFORMATION_SCHEMA].[COLUMNS] t1
FULL OUTER JOIN [myDB2].[INFORMATION_SCHEMA].[COLUMNS] t2
ON t1.COLUMN_NAME=t2.COLUMN_NAME
WHERE t2.COLUMN_NAME IS NULL
ORDER BY t1.COLUMN_NAME

-- columns in new database but not in old
SELECT t2.* FROM [myDB1].[INFORMATION_SCHEMA].[COLUMNS] t1
FULL OUTER JOIN [myDB2].[INFORMATION_SCHEMA].[COLUMNS] t2
ON t1.COLUMN_NAME=t2.COLUMN_NAME
WHERE t1.COLUMN_NAME IS NULL
ORDER BY t2.COLUMN_NAME

-- columns in old database but not in new
SELECT t1.* FROM [myDB1].[INFORMATION_SCHEMA].[COLUMNS] t1
FULL OUTER JOIN [myDB2].[INFORMATION_SCHEMA].[COLUMNS] t2
ON t1.COLUMN_NAME=t2.COLUMN_NAME
WHERE t2.COLUMN_NAME IS NULL
ORDER BY t1.COLUMN_NAME
-- columns in new database but not in old
SELECT t2.* FROM [myDB1].[INFORMATION_SCHEMA].[COLUMNS] t1
FULL OUTER JOIN [myDB2].[INFORMATION_SCHEMA].[COLUMNS] t2
ON t1.COLUMN_NAME=t2.COLUMN_NAME
WHERE t1.COLUMN_NAME IS NULL
ORDER BY t2.COLUMN_NAME

Method #3
And a further enhancement which lists the different datatypes, is_nullable, maxlength and collations side-by-side:

copy raw

-- all columns side by side
SELECT 
	t1.TABLE_CATALOG AS [DB1_Name],
	t2.TABLE_CATALOG AS [DB2_Name],
	t1.TABLE_NAME AS [DB1_Table],
	t2.TABLE_NAME AS [DB2_Table],
	t1.COLUMN_NAME AS [DB1_Column],
	t2.COLUMN_NAME AS [DB2_Column],
	t1.COLUMN_DEFAULT AS [DB1_ColumnDefault],
	t2.COLUMN_DEFAULT AS [DB2_ColumnDefault],
	t1.IS_NULLABLE AS [DB1_ColumnNulls],
	t2.IS_NULLABLE AS [DB2_ColumnNulls],
	t1.DATA_TYPE AS [DB1_ColumnDataType],
	t2.DATA_TYPE AS [DB2_ColumnDataType],
	t1.CHARACTER_MAXIMUM_LENGTH AS [DB1_ColumnMaxLength],
	t2.CHARACTER_MAXIMUM_LENGTH AS [DB2_ColumnMaxLength],
	t1.COLLATION_NAME AS [DB1_ColumnCollation],
	t2.COLLATION_NAME AS [DB2_ColumnCollation]
FROM [myDB1].[INFORMATION_SCHEMA].[COLUMNS] t1
FULL OUTER JOIN [myDB2].[INFORMATION_SCHEMA].[COLUMNS] t2
ON t1.TABLE_NAME=t2.TABLE_NAME AND t1.COLUMN_NAME=t2.COLUMN_NAME
WHERE 
	t1.COLUMN_DEFAULTt2.COLUMN_DEFAULT
	OR
	t1.IS_NULLABLEt2.IS_NULLABLE
	OR
	t1.DATA_TYPEt2.DATA_TYPE
	OR
	t1.CHARACTER_MAXIMUM_LENGTHt2.CHARACTER_MAXIMUM_LENGTH
	OR
	t1.COLLATION_NAMEt2.COLLATION_NAME
	OR
	(
		t1.TABLE_CATALOG IS NULL
		OR
		t2.TABLE_CATALOG IS NULL
	)

-- NOTE: This does not list if a column has been moved to another table

-- all columns side by side
SELECT
t1.TABLE_CATALOG AS [DB1_Name],
t2.TABLE_CATALOG AS [DB2_Name],
t1.TABLE_NAME AS [DB1_Table],
t2.TABLE_NAME AS [DB2_Table],
t1.COLUMN_NAME AS [DB1_Column],
t2.COLUMN_NAME AS [DB2_Column],
t1.COLUMN_DEFAULT AS [DB1_ColumnDefault],
t2.COLUMN_DEFAULT AS [DB2_ColumnDefault],
t1.IS_NULLABLE AS [DB1_ColumnNulls],
t2.IS_NULLABLE AS [DB2_ColumnNulls],
t1.DATA_TYPE AS [DB1_ColumnDataType],
t2.DATA_TYPE AS [DB2_ColumnDataType],
t1.CHARACTER_MAXIMUM_LENGTH AS [DB1_ColumnMaxLength],
t2.CHARACTER_MAXIMUM_LENGTH AS [DB2_ColumnMaxLength],
t1.COLLATION_NAME AS [DB1_ColumnCollation],
t2.COLLATION_NAME AS [DB2_ColumnCollation]
FROM [myDB1].[INFORMATION_SCHEMA].[COLUMNS] t1
FULL OUTER JOIN [myDB2].[INFORMATION_SCHEMA].[COLUMNS] t2
ON t1.TABLE_NAME=t2.TABLE_NAME AND t1.COLUMN_NAME=t2.COLUMN_NAME
WHERE
t1.COLUMN_DEFAULTt2.COLUMN_DEFAULT
OR
t1.IS_NULLABLEt2.IS_NULLABLE
OR
t1.DATA_TYPEt2.DATA_TYPE
OR
t1.CHARACTER_MAXIMUM_LENGTHt2.CHARACTER_MAXIMUM_LENGTH
OR
t1.COLLATION_NAMEt2.COLLATION_NAME
OR
(
t1.TABLE_CATALOG IS NULL
OR
t2.TABLE_CATALOG IS NULL
)
-- NOTE: This does not list if a column has been moved to another table

Method #4
And a final enhancement to epitomize my laziness (replace [myDB1] with name of database 1 and [myDB2] with name of database 2):

copy raw

-- Joins on table and column names, lists diffences if any
SELECT
        t1.TABLE_CATALOG AS [DB1_Name],
        t2.TABLE_CATALOG AS [DB2_Name],
        CASE
                WHEN t1.TABLE_NAME IS NULL AND t2.TABLE_NAME IS NOT NULL
                        THEN t2.TABLE_NAME + '   (new)'
                WHEN t1.TABLE_NAME IS NOT NULL AND t2.TABLE_NAME IS NULL
                        THEN t1.TABLE_NAME + '   (removed)'
                ELSE t1.TABLE_NAME
        END AS [Table_Name],
        CASE
                WHEN t1.COLUMN_NAME IS NULL AND t2.COLUMN_NAME IS NOT NULL
                        THEN t2.COLUMN_NAME + '   (new)'
                WHEN t1.COLUMN_NAME IS NOT NULL AND t2.COLUMN_NAME IS NULL
                        THEN t1.COLUMN_NAME + '   (removed)'
                ELSE t1.COLUMN_NAME
        END AS [Column_Name],
        CASE
                WHEN t1.COLUMN_DEFAULT IS NULL AND t2.COLUMN_DEFAULT IS NOT NULL
                        THEN t2.COLUMN_DEFAULT + '   (new)'
                WHEN t1.COLUMN_DEFAULT IS NOT NULL AND t2.COLUMN_DEFAULT IS NULL
                        THEN t1.COLUMN_DEFAULT + '   (removed)'
                WHEN t1.COLUMN_DEFAULTt2.COLUMN_DEFAULT
                        THEN 'Old [' + t1.COLUMN_DEFAULT + ']   New [' + t2.COLUMN_DEFAULT + ']'
                ELSE ISNULL(t1.COLUMN_DEFAULT, t2.COLUMN_DEFAULT)
        END AS [ColumnDefault],
        CASE
                WHEN t1.IS_NULLABLE IS NULL AND t2.IS_NULLABLE IS NOT NULL
                        THEN t2.IS_NULLABLE + '   (new)'
                WHEN t1.IS_NULLABLE IS NOT NULL AND t2.IS_NULLABLE IS NULL
                        THEN t1.IS_NULLABLE + '   (removed)'
                WHEN t1.IS_NULLABLEt2.IS_NULLABLE
                        THEN 'Old [' + t1.IS_NULLABLE + ']   New [' + t2.IS_NULLABLE + ']'
                ELSE ISNULL(t1.IS_NULLABLE, t2.IS_NULLABLE)
        END AS [ColumnNullable],
        CASE
                WHEN t1.DATA_TYPE IS NULL AND t2.DATA_TYPE IS NOT NULL
                        THEN t2.DATA_TYPE + '   (new)'
                WHEN t1.DATA_TYPE IS NOT NULL AND t2.DATA_TYPE IS NULL
                        THEN t1.DATA_TYPE + '   (removed)'
                WHEN t1.DATA_TYPEt2.DATA_TYPE
                        THEN 'Old [' + t1.DATA_TYPE + ']   New [' + t2.DATA_TYPE + ']'
                ELSE ISNULL(t1.DATA_TYPE, t2.DATA_TYPE)
        END AS [ColumnDataType],
        CASE
                WHEN t1.CHARACTER_MAXIMUM_LENGTH IS NULL AND t2.CHARACTER_MAXIMUM_LENGTH IS NOT NULL
                        THEN CAST(t2.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '   (new)'
                WHEN t1.CHARACTER_MAXIMUM_LENGTH IS NOT NULL AND t2.CHARACTER_MAXIMUM_LENGTH IS NULL
                        THEN CAST(t1.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '   (removed)'
                WHEN t1.CHARACTER_MAXIMUM_LENGTH IS NOT NULL
                        AND t2.CHARACTER_MAXIMUM_LENGTH IS NOT NULL
                        AND t1.CHARACTER_MAXIMUM_LENGTHt2.CHARACTER_MAXIMUM_LENGTH
                        THEN
                                'Old [' +
                                CAST(t1.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(11)) +
                                ']   New [' +
                                CAST(t2.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(11)) +
                                ']'
                ELSE NULL
        END AS [ColumnMaxLength],
        CASE
                WHEN t1.COLLATION_NAME IS NULL AND t2.COLLATION_NAME IS NOT NULL
                        THEN t2.COLLATION_NAME + '   (new)'
                WHEN t1.COLLATION_NAME IS NOT NULL AND t2.COLLATION_NAME IS NULL
                        THEN t1.COLLATION_NAME + '   (removed)'
                WHEN t1.COLLATION_NAMEt2.COLLATION_NAME
                        THEN 'Old [' + t1.COLLATION_NAME + ']   New [' + t2.COLLATION_NAME + ']'
                ELSE ISNULL(t1.COLLATION_NAME, t2.COLLATION_NAME)
        END AS [ColumnCollation]

FROM [myDB1].[INFORMATION_SCHEMA].[COLUMNS] t1
FULL OUTER JOIN [myDB2].[INFORMATION_SCHEMA].[COLUMNS] t2
ON t1.TABLE_NAME=t2.TABLE_NAME AND t1.COLUMN_NAME=t2.COLUMN_NAME
WHERE
        t1.COLUMN_DEFAULTt2.COLUMN_DEFAULT
        OR
        t1.IS_NULLABLEt2.IS_NULLABLE
        OR
        t1.DATA_TYPEt2.DATA_TYPE
        OR
        t1.CHARACTER_MAXIMUM_LENGTHt2.CHARACTER_MAXIMUM_LENGTH
        OR
        t1.COLLATION_NAMEt2.COLLATION_NAME
        OR
        (
                t1.TABLE_CATALOG IS NULL
                OR
                t2.TABLE_CATALOG IS NULL
        )
ORDER BY
        ISNULL(t1.TABLE_CATALOG,'ZZZZ'),
        ISNULL(t1.COLUMN_NAME,'ZZZZ'),
        ISNULL(t2.TABLE_CATALOG,'ZZZZ'),
        ISNULL(t2.COLUMN_NAME,'ZZZZ')

-- Joins on table and column names, lists diffences if any
SELECT
t1.TABLE_CATALOG AS [DB1_Name],
t2.TABLE_CATALOG AS [DB2_Name],
CASE
WHEN t1.TABLE_NAME IS NULL AND t2.TABLE_NAME IS NOT NULL
THEN t2.TABLE_NAME + ' (new)'
WHEN t1.TABLE_NAME IS NOT NULL AND t2.TABLE_NAME IS NULL
THEN t1.TABLE_NAME + ' (removed)'
ELSE t1.TABLE_NAME
END AS [Table_Name],
CASE
WHEN t1.COLUMN_NAME IS NULL AND t2.COLUMN_NAME IS NOT NULL
THEN t2.COLUMN_NAME + ' (new)'
WHEN t1.COLUMN_NAME IS NOT NULL AND t2.COLUMN_NAME IS NULL
THEN t1.COLUMN_NAME + ' (removed)'
ELSE t1.COLUMN_NAME
END AS [Column_Name],
CASE
WHEN t1.COLUMN_DEFAULT IS NULL AND t2.COLUMN_DEFAULT IS NOT NULL
THEN t2.COLUMN_DEFAULT + ' (new)'
WHEN t1.COLUMN_DEFAULT IS NOT NULL AND t2.COLUMN_DEFAULT IS NULL
THEN t1.COLUMN_DEFAULT + ' (removed)'
WHEN t1.COLUMN_DEFAULTt2.COLUMN_DEFAULT
THEN 'Old [' + t1.COLUMN_DEFAULT + '] New [' + t2.COLUMN_DEFAULT + ']'
ELSE ISNULL(t1.COLUMN_DEFAULT, t2.COLUMN_DEFAULT)
END AS [ColumnDefault],
CASE
WHEN t1.IS_NULLABLE IS NULL AND t2.IS_NULLABLE IS NOT NULL
THEN t2.IS_NULLABLE + ' (new)'
WHEN t1.IS_NULLABLE IS NOT NULL AND t2.IS_NULLABLE IS NULL
THEN t1.IS_NULLABLE + ' (removed)'
WHEN t1.IS_NULLABLEt2.IS_NULLABLE
THEN 'Old [' + t1.IS_NULLABLE + '] New [' + t2.IS_NULLABLE + ']'
ELSE ISNULL(t1.IS_NULLABLE, t2.IS_NULLABLE)
END AS [ColumnNullable],
CASE
WHEN t1.DATA_TYPE IS NULL AND t2.DATA_TYPE IS NOT NULL
THEN t2.DATA_TYPE + ' (new)'
WHEN t1.DATA_TYPE IS NOT NULL AND t2.DATA_TYPE IS NULL
THEN t1.DATA_TYPE + ' (removed)'
WHEN t1.DATA_TYPEt2.DATA_TYPE
THEN 'Old [' + t1.DATA_TYPE + '] New [' + t2.DATA_TYPE + ']'
ELSE ISNULL(t1.DATA_TYPE, t2.DATA_TYPE)
END AS [ColumnDataType],
CASE
WHEN t1.CHARACTER_MAXIMUM_LENGTH IS NULL AND t2.CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN CAST(t2.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ' (new)'
WHEN t1.CHARACTER_MAXIMUM_LENGTH IS NOT NULL AND t2.CHARACTER_MAXIMUM_LENGTH IS NULL
THEN CAST(t1.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ' (removed)'
WHEN t1.CHARACTER_MAXIMUM_LENGTH IS NOT NULL
AND t2.CHARACTER_MAXIMUM_LENGTH IS NOT NULL
AND t1.CHARACTER_MAXIMUM_LENGTHt2.CHARACTER_MAXIMUM_LENGTH
THEN
'Old [' +
CAST(t1.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(11)) +
'] New [' +
CAST(t2.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(11)) +
']'
ELSE NULL
END AS [ColumnMaxLength],
CASE
WHEN t1.COLLATION_NAME IS NULL AND t2.COLLATION_NAME IS NOT NULL
THEN t2.COLLATION_NAME + ' (new)'
WHEN t1.COLLATION_NAME IS NOT NULL AND t2.COLLATION_NAME IS NULL
THEN t1.COLLATION_NAME + ' (removed)'
WHEN t1.COLLATION_NAMEt2.COLLATION_NAME
THEN 'Old [' + t1.COLLATION_NAME + '] New [' + t2.COLLATION_NAME + ']'
ELSE ISNULL(t1.COLLATION_NAME, t2.COLLATION_NAME)
END AS [ColumnCollation]
FROM [myDB1].[INFORMATION_SCHEMA].[COLUMNS] t1
FULL OUTER JOIN [myDB2].[INFORMATION_SCHEMA].[COLUMNS] t2
ON t1.TABLE_NAME=t2.TABLE_NAME AND t1.COLUMN_NAME=t2.COLUMN_NAME
WHERE
t1.COLUMN_DEFAULTt2.COLUMN_DEFAULT
OR
t1.IS_NULLABLEt2.IS_NULLABLE
OR
t1.DATA_TYPEt2.DATA_TYPE
OR
t1.CHARACTER_MAXIMUM_LENGTHt2.CHARACTER_MAXIMUM_LENGTH
OR
t1.COLLATION_NAMEt2.COLLATION_NAME
OR
(
t1.TABLE_CATALOG IS NULL
OR
t2.TABLE_CATALOG IS NULL
)
ORDER BY
ISNULL(t1.TABLE_CATALOG,'ZZZZ'),
ISNULL(t1.COLUMN_NAME,'ZZZZ'),
ISNULL(t2.TABLE_CATALOG,'ZZZZ'),
ISNULL(t2.COLUMN_NAME,'ZZZZ')

Category: Transact-SQL :: Article: 522

Compare two databases using T-SQL

Credit where Credit is Due:

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support