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?

Answer 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):

HTH.

Answer 2

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

Answer 3

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.