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

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. — Andriy M
– Andriy M, Commented Mar 25, 2016 at 14:30

Károly Nagy · Accepted Answer · 2016-03-25 14:32:23Z

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).

answered Mar 25, 2016 at 14:32

Károly Nagy

3,0901 gold badge15 silver badges13 bronze badges

Hi. Thanks for proposing a solution but this is not giving the accurate count.

romil gaurav
– romil gaurav

2016-03-28 09:48:56 +00:00
Commented Mar 28, 2016 at 9:48

Add a comment |

Stack Exchange Network

Schema name, table name and row count

1 Answer 1

Your Answer

Hot Network Questions

Schema name, table name and row count

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions