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

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

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.

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;