List table columns with their foreign keys in Vertica database

This query shows all columns in database with corresponding them columns from referenced table if there is any.

Query

select col.table_schema || '.' || col.table_name as table,
       col.ordinal_position as pos,
       col.column_name,
       case when fks.constraint_id is not null
            then '>-' end as rel,
       fks.reference_table_schema || '.' || fks.reference_table_name
            as primary_table,
       fks.reference_column_name as primary_column,
       fks.constraint_name
from v_catalog.columns col
left join v_catalog.foreign_keys fks
          on col.table_schema = fks.table_schema
          and col.table_name = fks.table_name
          and col.column_name = fks.column_name
order by col.table_schema,
         col.table_name,
         col.ordinal_position;

Columns

  • table - schema name and table name
  • pos - column position within table
  • column_name - name of the column
  • rel - relationship symbol ('>-') indicating foreign key and direction
  • primary_table - referenced table name preceded by schema name
  • primary_column - column name corresponding to FK column
  • constraint_name - foreign key constraint name

Rows

  • One row represents one column
  • Scope of rows: all columns a database
  • Ordered by schema name of the table, column position within table

Sample results

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