MYSQL - count number of rows in each table

Question

I would like to know how many rows are in each table in my database. I've come so far as to having

select count(*) _tablename_;

However i would need to do that on each and every table - and there are a lot. What would me the best way to get a print-out with the table name and it's row count?

moinudin · Accepted Answer · 2011-01-05 11:08:49Z

117

SELECT table_name, table_rows
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = '<your db>';

I also hope you realise there's an error in your query: it's missing a FROM.

edited Jan 5, 2011 at 11:08

answered Jan 5, 2011 at 11:01

moinudin

139k45 gold badges195 silver badges219 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Redconnection Over a year ago

Thanks a bunch. I've got the part that it's better to get the list from metadata!

Don Over a year ago

Oops... I tried this query but it gives unstable results (each time different values)!

Andrew Over a year ago

Yes, the inaccuracy of InnoDB row counts is a by-product of that engine as mentioned here: dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html

Jeremy Logan Over a year ago

Specifically: "InnoDB does not keep an internal count of rows in a table because concurrent transactions might “see” different numbers of rows at the same time. [...] If an approximate row count is sufficient, SHOW TABLE STATUS can be used."

pjammer Over a year ago

you can also improve this a bit by using format(table_rows) and getting some ,'s in there so you can read the row count easier.

|

Kowsalya · Accepted Answer · 2018-07-03 11:29:18Z

4

This following query will return number of rows in each table but it doesn't seem to return exact value all the time

SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '<your db>';

TABLE_ROWS: The number of table rows in the partition. For partitioned InnoDB tables, the row count given in the TABLE_ROWS column is only an estimated value used in SQL optimization, and may not always be exact... for more https://dev.mysql.com/doc/mysql-infoschema-excerpt/5.5/en/partitions-table.html

edited Jul 3, 2018 at 11:29

Kowsalya

1,40617 silver badges25 bronze badges

answered Jul 3, 2018 at 10:14

Sarita Ishwarkar

1291 silver badge2 bronze badges

1 Comment

Abdur Rehman Over a year ago

how it is different from the above answer?

Thom A · Accepted Answer · 2025-05-16 15:00:11Z

1

In addition to SQL queries by others, one can also use Workbench GUI to get the row-counts of each table. To do this, Launch Workbench -> Connect to Db -> right click Db and select "Schema Inspector" (as in Screenshot below - I have highlighted the "Rows" column):

edited May 16 at 15:00

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

answered Apr 12, 2020 at 20:39

Eddie Kumar

1,53720 silver badges23 bronze badges

Collectives™ on Stack Overflow

MYSQL - count number of rows in each table

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related