How to Find Orphan Tables in a Database

An orphan table, in the schema sense, is one with no foreign key relationship — incoming or outgoing — to any other table in the database. It just sits there, structurally disconnected from everything around it.

Some orphan tables are completely intentional: a config key-value store, an audit_log table that references entities loosely by string ID instead of a real constraint, a lookup table seeded once and never joined against directly. Others are leftovers — a table from a feature that got ripped out, a one-off migration scratch table, a duplicate that was supposed to replace an older one but never got cleaned up. The problem is that from the schema alone, you can't tell which is which. That's a review list, not an automatic delete list.

Why it's worth checking periodically

Schemas accumulate entropy. Every orphan table is either dead weight worth dropping, a missing relationship that should have a real foreign key, or a legitimate design choice that's worth documenting so the next person doesn't have to re-investigate it. None of those outcomes happen if nobody ever generates the list.

Finding them in Postgres

The query below lists every base table in the public schema, then excludes anything that appears on either side of a declared foreign key constraint:

WITH fk_tables AS (
  SELECT tc.table_name AS tbl FROM information_schema.table_constraints tc
  WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_schema = 'public'
  UNION
  SELECT ccu.table_name AS tbl
  FROM information_schema.table_constraints tc
  JOIN information_schema.constraint_column_usage ccu
    ON tc.constraint_name = ccu.constraint_name
  WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_schema = 'public'
)
SELECT t.table_name
FROM information_schema.tables t
WHERE t.table_schema = 'public'
  AND t.table_type = 'BASE TABLE'
  AND t.table_name NOT IN (SELECT tbl FROM fk_tables)
ORDER BY t.table_name;

This only catches relationships Postgres actually enforces. If your schema leans on naming conventions instead of real constraints — see detecting missing foreign keys — a table can look orphaned here while still being conceptually related to others through an unenforced _id column. Run both checks together for an accurate picture.

Triaging what comes back

For each table in the result, it usually falls into one of four buckets:

• Genuinely standalone by design — config, feature flags, reference/lookup data. No action needed, but worth a one-line comment on the table saying so, for the next person who runs this query.
• Related, but unenforced — the relationship exists in application logic or a loosely typed column, and belongs in the missing-foreign-keys check instead.
• Dead — no rows added in a long time, no code referencing it. A candidate to drop, after confirming nothing reads from it directly via raw SQL that wouldn't show up in an ORM-model search.
• A duplicate or an artifact of an incomplete migration — often named suspiciously close to another table (users_old, users_v2, users_tmp).

Seeing it instead of querying for it

Once a schema has more than a handful of tables, a text list of orphan table names is harder to reason about than seeing them directly on a diagram — a table sitting off to the side with zero connecting lines is immediate and visual in a way a query result isn't. ERD Factory's Discovery view and structural gap detection flag orphan tables automatically as soon as you paste in a DDL dump, alongside missing foreign keys and missing indexes, so the triage above starts from a diagram instead of a query result.