Blog | Programming Notes

Blog ❯ Author: Fabio Moschini|Date: 13.10.2022

Comparing database structures

DatabaseSql

How to Compare Database Structures

In the previous article, we saw how to use INFORMATION_SCHEMA to get information about a database's metadata.
Now we'll see how to use this tool to compare the structures of two databases with the goal of highlighting the differences between them.

JOIN: Quick Review

Through the use of JOINs, it's possible to read correlated data between two or more tables.
JOIN conditions tell the SQL engine how to read rows from one table based on the data present in the rows of another table and are expressed through SQL commands:

- INNER JOIN: selects and relates rows from two tables for which there is a match of values in the columns indicated for correlation:
- LEFT OUTER JOIN (LEFT JOIN): selects all rows from the first table (the left one) with the rows from the second table (the right one) that satisfy the join condition. Rows from the first table without a match in the second are extended with null values:
- RIGHT OUTER JOIN (RIGHT JOIN): works symmetrically to LEFT OUTER JOIN, meaning it selects all rows from the second table (the right one) with the rows from the first table (the left one) that satisfy the join condition. Rows from the second table without a match in the first are extended with null values:
- FULL OUTER JOIN (FULL JOIN): selects all rows from both the first and second tables, obtained with LEFT OUTER JOIN and RIGHT OUTER JOIN:

Which JOIN to Use for Our Objective?

Through the FULL OUTER JOIN operation (equivalent to FULL JOIN) we can determine the tables defined only in one of the two databases.
To do this, it's sufficient to apply this type of JOIN to the tables DB_Source.INFORMATION_SCHEMA.TABLES and DB_Target.INFORMATION_SCHEMA.TABLES, where DB_Source and DB_Target are the two databases to analyze:

-- Search for different tables
SELECT ISNULL(SRC.TABLE_TYPE, TGT.TABLE_TYPE) AS [TYPE],
       ISNULL(SRC.TABLE_SCHEMA, TGT.TABLE_SCHEMA) AS [SCHEMA],
       ISNULL(SRC.TABLE_NAME, TGT.TABLE_NAME) AS [NAME], 
       'Defined in ' + CASE WHEN SRC.TABLE_CATALOG IS NULL THEN 'Target' ELSE 'Source' END + 
       ' but missing in ' + CASE WHEN SRC.TABLE_CATALOG IS NULL THEN 'Source' ELSE 'Target' END AS [NOTE]
FROM DB_Source.INFORMATION_SCHEMA.TABLES AS SRC
  FULL OUTER JOIN DB_Target.INFORMATION_SCHEMA.TABLES AS TGT
    ON SRC.TABLE_SCHEMA = TGT.TABLE_SCHEMA
    AND SRC.TABLE_NAME = TGT.TABLE_NAME
    AND SRC.TABLE_TYPE = TGT.TABLE_TYPE
WHERE SRC.TABLE_CATALOG IS NULL
OR TGT.TABLE_CATALOG IS NULL

The query result is:

Similarly, we can determine differences at the column level as well:

-- Search for different columns on same tables
SELECT SRC.TABLE_CATALOG, SRC.TABLE_SCHEMA, SRC.TABLE_NAME, SRC.COLUMN_NAME,
       TGT.TABLE_CATALOG, TGT.TABLE_SCHEMA, TGT.TABLE_NAME, TGT.COLUMN_NAME,
       '||',*
FROM DB_Source.INFORMATION_SCHEMA.COLUMNS AS SRC
  FULL OUTER JOIN DB_Target.INFORMATION_SCHEMA.COLUMNS AS TGT
    ON SRC.TABLE_SCHEMA = TGT.TABLE_SCHEMA
    AND SRC.TABLE_NAME = TGT.TABLE_NAME
    AND SRC.COLUMN_NAME = TGT.COLUMN_NAME
WHERE ISNULL(SRC.TABLE_SCHEMA, '') <> ISNULL(TGT.TABLE_SCHEMA, '')
OR ISNULL(SRC.TABLE_NAME, '') <> ISNULL(TGT.TABLE_NAME, '')
OR ISNULL(SRC.COLUMN_NAME, '') <> ISNULL(TGT.COLUMN_NAME, '')

This script highlights the columns defined only in one of the two databases. All columns belonging to tables present only in one database are also listed. The query result is:

Using the same technique, it's possible to determine differences also regarding other types of entities defined on a database, such as integrity constraints, indexes, primary keys, stored procedures or functions, triggers.