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