How can I find tablename and row count of all the tables?

Question

I want to find tablename and row count of all the tables in a database in mysql and pgsql by using query. Is there any query to find this?

Answer 1

The SQL-standard INFORMATION_SCHEMA provides information about the structure of your database - which tables it has, etc. It does not, however, contain row counts.

At least for PostgreSQL you have at least two options for getting row counts:

• Use an external program or a PL/PgSQL function that generates dynamic SQL using EXECUTE to do a SELECT count(*) FROM tablename for each table found in the INFORMATION_SCHEMA (excluding system tables); or

• Run ANALYZE then get the approximate rowcounts from the PostgreSQL statistics tables. This approach is a lot faster, but is only getting an approximate table rowcount based on statistical sampling and estimation.

This has been discussed in detail for PostgreSQL here.

The approach of querying INFORMATION_SCHEMA for a table list and then looping over the tables doing count should be portable across all databases. Other approaches will likely require various degrees of database-specific code.

Answer 2

For postgresql:

SELECT 
nspname AS schema,relname table_name,reltuples::int rows_count

FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)

WHERE 

  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r'  and reltuples>0
ORDER BY relname  ;