List accurate row counts for all tables in a PostgreSQL database

all_row_counts.sql

DO $$
DECLARE
  schema text;
  tbl text;
  rows integer;
BEGIN
  FOR schema, tbl IN
    SELECT schemaname, tablename
    FROM pg_tables
    WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
    ORDER BY schemaname, tablename
  LOOP
    BEGIN
      EXECUTE format('SELECT count(*) FROM %I.%I', schema, tbl) INTO rows;
      RAISE NOTICE '%.%: % rows', schema, tbl, rows;
    EXCEPTION
      WHEN insufficient_privilege THEN RAISE NOTICE '%.%: permission denied', schema, tbl;
    END;
  END LOOP;
END $$;