Query below compares columns (names) in tables between two IBM DB2 schemas. It shows columns missing in either of two schema.
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;
Replace SCHEMA_1 and SCHEMA_2 with names of two of your schemas (on IBM DB2 instance) that you'd like to compare.
- 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)
- 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