Compare tables and columns in two databases in Teradata database

Article for: SQL Server MySQL

Query below compares columns (names) in tables between two databases. It shows columns missing in either of two databases.

Query

SELECT  COALESCE(db1.TableName, db2.TableName) as TableName,
        COALESCE(db1.ColumnName, db2.ColumnName) as ColumnName,
        db1.ColumnName as Database1,
        db2.ColumnName as Database2
FROM    (SELECT col.TableName,
                col.ColumnName
        FROM    DBC.ColumnsV col
        JOIN    DBC.TablesV tab
        ON  col.DatabaseName = tab.DatabaseName
        AND col.TableName = tab.TableName
        AND tab.TableKind = 'T'
        WHERE   col.DatabaseName = 'DATABASE_1'
        ) db1
FULL JOIN   (SELECT col.TableName,
                    col.ColumnName
            FROM    DBC.ColumnsV col
            JOIN    DBC.TablesV tab
            ON  col.DatabaseName = tab.DatabaseName
            AND col.TableName = tab.TableName
            AND tab.TableKind = 'T'
            WHERE   col.DatabaseName = 'DATABASE_2'
            ) db2
ON db1.TableName = db2.TableName
AND db1.ColumnName = db2.ColumnName
WHERE db1.ColumnName IS NULL
OR db2.ColumnName IS NULL
ORDER BY 1;

Instruction

Replace DATABASE_1 and DATABASE_2 with names of two of your databases (on Teradata instance) that you'd like to compare.

Columns

  • TableName - name of the table
  • ColumnName - name of column
  • Database1 - if column exists in a table in database 1 then column contains its name (repeats it from column column)
  • Database2 - if column exists in a table in database 2 then column contains its name (repeats it from column column)

Rows

  • One row represents one distinct name of column in specific table. Row represents column if it exist only in one database.
  • Scope of rows: all distinct schema, table and column names in both databases.
  • Ordered by table name and column name

Sample results

0
There are no comments. Click here to write the first comment.