Query below compares names of columns in tables between two schemas. It shows only columns missing in any of provided schemas.
select coalesce(c1.table_name, c2.table_name) as table_name, coalesce(c1.column_name, c2.column_name) as table_column, c1.column_name as schema1, c2.column_name as schema2 from (select table_name, column_name from v_catalog.columns c where c.table_schema = 'schema1') c1 -- put schema name here full join (select table_name, column_name from v_catalog.columns c where c.table_schema = 'schema2') c2 -- put schema name to compare here on c1.table_name = c2.table_name and c1.column_name = c2.column_name where c1.column_name is null or c2.column_name is null order by table_name, table_column;
Change schema1 and schema2 to chosen schema names you want to compare.
- table_name - name of the table with schema
- table_column - name of column
- schema1 - if column exists in a table in schema 1 then column contains its name
- schema2 - if column exists in a table in schema 2 then column contains its name
- 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 table and column names in both schemas.
- Ordered by table name and column name