Compare tables and columns in two schemas in Vertica database

Query below compares names of columns in tables between two schemas. It shows only columns missing in any of provided schemas.

Query

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;

Instruction

Change schema1 and schema2 to chosen schema names you want to compare.

Columns

  • 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

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 table and column names in both schemas.
  • Ordered by table name and column name

Sample results

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