Gok Demir Gok Demir - 1 year ago 78
SQL Question

Getting tables with no rows without counting

I got a huge PostgreSQL database with lots of tables. I want learn all empty tables without counting each tables for performance reasons (Some of the tables have several millions rows).

jmz jmz
Answer Source

This query will give you an approximate result, but does not include counting table rows.

SELECT relname FROM pg_class JOIN pg_namespace ON (pg_class.relnamespace = pg_namespace.oid) WHERE relpages = 0 AND pg_namespace.nspname = 'public';

This will work best after a VACUUM ANALYZE.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download