Compare two databases using T-SQL

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:
copyraw
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
  1.  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:
copyraw
-- 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
  1.  -- columns in old database but not in new 
  2.  SELECT t1.* FROM [myDB1].[INFORMATION_SCHEMA].[COLUMNS] t1 
  3.  FULL OUTER JOIN [myDB2].[INFORMATION_SCHEMA].[COLUMNS] t2 
  4.  ON t1.COLUMN_NAME=t2.COLUMN_NAME 
  5.  WHERE t2.COLUMN_NAME IS NULL 
  6.  ORDER BY t1.COLUMN_NAME 
  7.   
  8.  -- columns in new database but not in old 
  9.  SELECT t2.* FROM [myDB1].[INFORMATION_SCHEMA].[COLUMNS] t1 
  10.  FULL OUTER JOIN [myDB2].[INFORMATION_SCHEMA].[COLUMNS] t2 
  11.  ON t1.COLUMN_NAME=t2.COLUMN_NAME 
  12.  WHERE t1.COLUMN_NAME IS NULL 
  13.  ORDER BY t2.COLUMN_NAME 

Method #3
And a further enhancement which lists the different datatypes, is_nullable, maxlength and collations side-by-side:
copyraw
-- 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
  1.  -- all columns side by side 
  2.  SELECT 
  3.      t1.TABLE_CATALOG AS [DB1_Name], 
  4.      t2.TABLE_CATALOG AS [DB2_Name], 
  5.      t1.TABLE_NAME AS [DB1_Table], 
  6.      t2.TABLE_NAME AS [DB2_Table], 
  7.      t1.COLUMN_NAME AS [DB1_Column], 
  8.      t2.COLUMN_NAME AS [DB2_Column], 
  9.      t1.COLUMN_DEFAULT AS [DB1_ColumnDefault], 
  10.      t2.COLUMN_DEFAULT AS [DB2_ColumnDefault], 
  11.      t1.IS_NULLABLE AS [DB1_ColumnNulls], 
  12.      t2.IS_NULLABLE AS [DB2_ColumnNulls], 
  13.      t1.DATA_TYPE AS [DB1_ColumnDataType], 
  14.      t2.DATA_TYPE AS [DB2_ColumnDataType], 
  15.      t1.CHARACTER_MAXIMUM_LENGTH AS [DB1_ColumnMaxLength], 
  16.      t2.CHARACTER_MAXIMUM_LENGTH AS [DB2_ColumnMaxLength], 
  17.      t1.COLLATION_NAME AS [DB1_ColumnCollation], 
  18.      t2.COLLATION_NAME AS [DB2_ColumnCollation] 
  19.  FROM [myDB1].[INFORMATION_SCHEMA].[COLUMNS] t1 
  20.  FULL OUTER JOIN [myDB2].[INFORMATION_SCHEMA].[COLUMNS] t2 
  21.  ON t1.TABLE_NAME=t2.TABLE_NAME AND t1.COLUMN_NAME=t2.COLUMN_NAME 
  22.  WHERE 
  23.      t1.COLUMN_DEFAULTt2.COLUMN_DEFAULT 
  24.      OR 
  25.      t1.IS_NULLABLEt2.IS_NULLABLE 
  26.      OR 
  27.      t1.DATA_TYPEt2.DATA_TYPE 
  28.      OR 
  29.      t1.CHARACTER_MAXIMUM_LENGTHt2.CHARACTER_MAXIMUM_LENGTH 
  30.      OR 
  31.      t1.COLLATION_NAMEt2.COLLATION_NAME 
  32.      OR 
  33.      ( 
  34.          t1.TABLE_CATALOG IS NULL 
  35.          OR 
  36.          t2.TABLE_CATALOG IS NULL 
  37.      ) 
  38.   
  39.  -- 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):
copyraw
-- 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')
  1.  -- Joins on table and column names, lists diffences if any 
  2.  SELECT 
  3.          t1.TABLE_CATALOG AS [DB1_Name], 
  4.          t2.TABLE_CATALOG AS [DB2_Name], 
  5.          CASE 
  6.                  WHEN t1.TABLE_NAME IS NULL AND t2.TABLE_NAME IS NOT NULL 
  7.                          THEN t2.TABLE_NAME + '   (new)' 
  8.                  WHEN t1.TABLE_NAME IS NOT NULL AND t2.TABLE_NAME IS NULL 
  9.                          THEN t1.TABLE_NAME + '   (removed)' 
  10.                  ELSE t1.TABLE_NAME 
  11.          END AS [Table_Name], 
  12.          CASE 
  13.                  WHEN t1.COLUMN_NAME IS NULL AND t2.COLUMN_NAME IS NOT NULL 
  14.                          THEN t2.COLUMN_NAME + '   (new)' 
  15.                  WHEN t1.COLUMN_NAME IS NOT NULL AND t2.COLUMN_NAME IS NULL 
  16.                          THEN t1.COLUMN_NAME + '   (removed)' 
  17.                  ELSE t1.COLUMN_NAME 
  18.          END AS [Column_Name], 
  19.          CASE 
  20.                  WHEN t1.COLUMN_DEFAULT IS NULL AND t2.COLUMN_DEFAULT IS NOT NULL 
  21.                          THEN t2.COLUMN_DEFAULT + '   (new)' 
  22.                  WHEN t1.COLUMN_DEFAULT IS NOT NULL AND t2.COLUMN_DEFAULT IS NULL 
  23.                          THEN t1.COLUMN_DEFAULT + '   (removed)' 
  24.                  WHEN t1.COLUMN_DEFAULTt2.COLUMN_DEFAULT 
  25.                          THEN 'Old [' + t1.COLUMN_DEFAULT + ']   New [' + t2.COLUMN_DEFAULT + ']' 
  26.                  ELSE ISNULL(t1.COLUMN_DEFAULT, t2.COLUMN_DEFAULT) 
  27.          END AS [ColumnDefault], 
  28.          CASE 
  29.                  WHEN t1.IS_NULLABLE IS NULL AND t2.IS_NULLABLE IS NOT NULL 
  30.                          THEN t2.IS_NULLABLE + '   (new)' 
  31.                  WHEN t1.IS_NULLABLE IS NOT NULL AND t2.IS_NULLABLE IS NULL 
  32.                          THEN t1.IS_NULLABLE + '   (removed)' 
  33.                  WHEN t1.IS_NULLABLEt2.IS_NULLABLE 
  34.                          THEN 'Old [' + t1.IS_NULLABLE + ']   New [' + t2.IS_NULLABLE + ']' 
  35.                  ELSE ISNULL(t1.IS_NULLABLE, t2.IS_NULLABLE) 
  36.          END AS [ColumnNullable], 
  37.          CASE 
  38.                  WHEN t1.DATA_TYPE IS NULL AND t2.DATA_TYPE IS NOT NULL 
  39.                          THEN t2.DATA_TYPE + '   (new)' 
  40.                  WHEN t1.DATA_TYPE IS NOT NULL AND t2.DATA_TYPE IS NULL 
  41.                          THEN t1.DATA_TYPE + '   (removed)' 
  42.                  WHEN t1.DATA_TYPEt2.DATA_TYPE 
  43.                          THEN 'Old [' + t1.DATA_TYPE + ']   New [' + t2.DATA_TYPE + ']' 
  44.                  ELSE ISNULL(t1.DATA_TYPE, t2.DATA_TYPE) 
  45.          END AS [ColumnDataType], 
  46.          CASE 
  47.                  WHEN t1.CHARACTER_MAXIMUM_LENGTH IS NULL AND t2.CHARACTER_MAXIMUM_LENGTH IS NOT NULL 
  48.                          THEN CAST(t2.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '   (new)' 
  49.                  WHEN t1.CHARACTER_MAXIMUM_LENGTH IS NOT NULL AND t2.CHARACTER_MAXIMUM_LENGTH IS NULL 
  50.                          THEN CAST(t1.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '   (removed)' 
  51.                  WHEN t1.CHARACTER_MAXIMUM_LENGTH IS NOT NULL 
  52.                          AND t2.CHARACTER_MAXIMUM_LENGTH IS NOT NULL 
  53.                          AND t1.CHARACTER_MAXIMUM_LENGTHt2.CHARACTER_MAXIMUM_LENGTH 
  54.                          THEN 
  55.                                  'Old [' + 
  56.                                  CAST(t1.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(11)) + 
  57.                                  ']   New [' + 
  58.                                  CAST(t2.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(11)) + 
  59.                                  ']' 
  60.                  ELSE NULL 
  61.          END AS [ColumnMaxLength], 
  62.          CASE 
  63.                  WHEN t1.COLLATION_NAME IS NULL AND t2.COLLATION_NAME IS NOT NULL 
  64.                          THEN t2.COLLATION_NAME + '   (new)' 
  65.                  WHEN t1.COLLATION_NAME IS NOT NULL AND t2.COLLATION_NAME IS NULL 
  66.                          THEN t1.COLLATION_NAME + '   (removed)' 
  67.                  WHEN t1.COLLATION_NAMEt2.COLLATION_NAME 
  68.                          THEN 'Old [' + t1.COLLATION_NAME + ']   New [' + t2.COLLATION_NAME + ']' 
  69.                  ELSE ISNULL(t1.COLLATION_NAME, t2.COLLATION_NAME) 
  70.          END AS [ColumnCollation] 
  71.   
  72.  FROM [myDB1].[INFORMATION_SCHEMA].[COLUMNS] t1 
  73.  FULL OUTER JOIN [myDB2].[INFORMATION_SCHEMA].[COLUMNS] t2 
  74.  ON t1.TABLE_NAME=t2.TABLE_NAME AND t1.COLUMN_NAME=t2.COLUMN_NAME 
  75.  WHERE 
  76.          t1.COLUMN_DEFAULTt2.COLUMN_DEFAULT 
  77.          OR 
  78.          t1.IS_NULLABLEt2.IS_NULLABLE 
  79.          OR 
  80.          t1.DATA_TYPEt2.DATA_TYPE 
  81.          OR 
  82.          t1.CHARACTER_MAXIMUM_LENGTHt2.CHARACTER_MAXIMUM_LENGTH 
  83.          OR 
  84.          t1.COLLATION_NAMEt2.COLLATION_NAME 
  85.          OR 
  86.          ( 
  87.                  t1.TABLE_CATALOG IS NULL 
  88.                  OR 
  89.                  t2.TABLE_CATALOG IS NULL 
  90.          ) 
  91.  ORDER BY 
  92.          ISNULL(t1.TABLE_CATALOG,'ZZZZ'), 
  93.          ISNULL(t1.COLUMN_NAME,'ZZZZ'), 
  94.          ISNULL(t2.TABLE_CATALOG,'ZZZZ'), 
  95.          ISNULL(t2.COLUMN_NAME,'ZZZZ') 

Category: Transact-SQL :: Article: 522

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.