Query below compares columns (names) in tables between two IBM DB2 schemas. It shows columns missing in either of two schema.

Query

select coalesce(c1.tabname, c2.tabname) as table_name,
       coalesce(c1.colname, c2.colname) as table_column,
       c1.colname as schema1,
       c2.colname as schema2
from
    (select tabname,
            colname
     from syscat.columns c
     where c.tabschema = 'SCHEMA_1') c1
full join
         (select tabname,
                 colname
          from syscat.columns c
          where c.tabschema = 'SCHEMA_2') c2
on c1.tabname = c2.tabname and c1.colname = c2.colname
where c1.colname is null
      or c2.colname is null
order by table_name,
         table_column;

Instruction

Replace SCHEMA_1 and SCHEMA_2 with names of two of your schemas (on IBM DB2 instance) that you'd like to compare.

Columns

• table_name - name of the table
• table_column - name of column
• schema1 - if column exists in a table in schema 1 then column contains its name (repeats it from column column)
• schema2 - 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. Row represents column if it exist only in one schema.
• Scope of rows: all distinct schema, table and column names in both databases.
• Ordered by table name and column name

Sample results