MySQL - How to count all rows per table in one query

Is there a way to query the DB to find out how many rows there are in all the tables?

i.e.

table1 1234
table2 222
table3 7888

Hope you can advise

Solution 1:

SELECT 
    TABLE_NAME, 
    TABLE_ROWS 
FROM 
    `information_schema`.`tables` 
WHERE 
    `table_schema` = 'YOUR_DB_NAME';

Solution 2:

The above will give you an approximation, but if you want an exact count, it can be done in two steps. First, execute a query like the following:

select concat("select '",table_name,"', count(*) from ",table_name,";") 
from `information_schema`.`tables` 
WHERE `table_schema` = '[your schema here]';

That will produce a list of SQL statements, one for each table in your database, you can then run to get an exact count.

Solution 3:

Synthesising the info above and this post into one set of queries, we get a self-writing query that will give accurate row counts:

SET @tableSchema = 'my_schema';
SET SESSION group_concat_max_len = 10000000;
SET @rowCounts = (
  SELECT group_concat(CONCAT('SELECT ''',TABLE_NAME,''', COUNT(*) FROM ', TABLE_NAME) SEPARATOR ' union all ')
  FROM information_schema.tables WHERE table_schema = @tableSchema
);
PREPARE statement FROM @rowCounts;
EXECUTE statement;

-- don't run dealloc until you've exported your results ;)
DEALLOCATE PREPARE statement;

Solution 4:

This will give you the exact Table name and count on a single list

SELECT CONCAT('SELECT ''',table_name,''', COUNT(*) FROM ', table_name, ' union all') 
      FROM information_schema.tables WHERE table_schema = 'clw';

Solution 5:

SELECT 
    table_name, 
    table_rows 
FROM 
    INFORMATION_SCHEMA.TABLES