This query returns tables which are not referenced by any foreign key.
select '>- no FKs' as foreign_keys, tab.table_schema, tab.table_name from information_schema.tables tab where tab.table_schema not in ('information_schema', 'pg_catalog') and tab.table_type ='BASE TABLE' and tab.table_schema || '.' || tab.table_name not in (select distinct tco.table_schema || '.' ||tco.table_name from information_schema.referential_constraints rco join information_schema.table_constraints tco on rco.unique_constraint_name = tco.constraint_name and rco.unique_constraint_schema = tco.table_schema) order by tab.table_schema, tab.table_name;
- foreign_keys - symbol indicating lack of FK references
- table_schema - name of the schema
- table_name - name of the table
- One row represents one table
- Scope of rows: all tables in a database that are not referenced by the foreign keys
- Ordered by table schema and table name
Tables not referenced by foreign keys in sample pagila database.