Compare tables and columns in two schemas in Oracle database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-06-03

Table of Contents:


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

    Query

    select nvl(s1.table_name, s2.table_name) as table_name,
           nvl(s1.column_name, s2.column_name) as column_name,
           s1.column_name as schema_1,
           s2.column_name as schema_2
    from ( select table_name,
                  column_name
           from sys.all_tab_cols
           where owner = 'schema_1'       -- put schema name to compare here
    ) s1
    full join ( select table_name,
                       column_name
                from sys.all_tab_cols
                where owner = 'schema_2'  -- put schema name to compare here
    ) s2 on s2.table_name = s1.table_name
         and s2.column_name = s1.column_name
    where s1.column_name is null
          or s2.column_name is null
    order by table_name,
             column_name;
    

    Columns

    • table_name - name of the table with schema
    • column_name - name of column
    • schema_1 - if column exists in a table in schema 1 then column contains its name (repeats it from column column)
    • schema_2 - if column exists in a table in schema 2 then column contains its name (repeats it from column column)

    Rows

    • One row represents one distinct name of column in specific table.
    • Scope of rows: all distinct columns in that exist only in one of the compared databases.
    • Ordered by table and column name

    Sample results

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

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept