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.


select col.table_schema || '.' || col.table_name as table,
       col.ordinal_position as pos,
       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,
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,


  • 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


  • 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

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