Schema name, table name and row count

Question

My database has 50+ schemas. Each schema has around 100 tables and some views.

I want a query that tells me the schema name, table name, and total number of rows in each table. The below query:

select table_schema, table_name, table_type, count(*) 
from information_schema.tables 
group by table_schema, table_name, table_type

...gives the total number of tables with each name, rather than giving the row count. I am using pgAdmin 3 version 1.10

• Andriy M: Normally you get the number of rows in a table by querying the table itself: SELECT COUNT(*) FROM tablename. Unless PostgreSQL stores row counts externally somewhere, you will probably need to use dynamic SQL: generate a SELECT COUNT(*) FROM... query for every table of every schema listed in information_schema.tables and then execute the resulting script.

Answer 1

This should do the trick:

select nspname, relname, relkind, reltuples from pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
where relkind in ('r', 'v');

Note that the reltuples is an estimate of the rows not the actual count at the moment but generally it's quite accurate (depends on how up to date your statistics are).

• romil gaurav: Hi. Thanks for proposing a solution but this is not giving the accurate count.