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

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;

SELECT 
    table_name, 
    table_rows 
FROM 
    INFORMATION_SCHEMA.TABLES

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';

select sum(cnt) from
(
select count(*) as cnt from table1
union ALL
select count(*) as cnt from table2
union ALL
select count(*) as cnt from table3 
)t1

It is convenient to use a stored procedure to get tables' rows. For example:

CALL database_tables_row_count('my_shop_db');

will display:

+-------------------+-----------+
| table             | row_count |
+-------------------+-----------+
| user              |         5 |
| payment           |        12 |
+-------------------+-----------+

in case there are no tables inside 'my_shop_db' you'll get:

Empty set (0.00 sec)

If you misspell the database name you'll get:

ERROR 1049 (42000): Unknown database 'my_so_db'

The same way as if you issued the statement use non_existing_db;

The stored procedure must be stored somewhere (in a database). If you store it into the current database, you will be able to use it this way

CALL database_tables_row_count('my_shop_db');

to get the results regarding any database as long you use your current database where you stored that procedure

Since such query like a count of tables' rows is quite common so you may want to store that procedure in a common database (a kind of a toolbox) for eg called admin. To create the stored procedure inside a new db:

CREATE DATABASE IF NOT EXISTS `admin`;

then switch into it:

USE `admin`;

and create the stored procedure:

DROP PROCEDURE IF EXISTS `database_tables_row_count`;

DELIMITER $$
CREATE PROCEDURE `database_tables_row_count`(IN tableSchema VARCHAR(255))
BEGIN
    DECLARE msg VARCHAR(128);

    IF (SELECT COUNT(TABLE_NAME) FROM information_schema.tables WHERE table_schema = `tableSchema`) = 0 THEN
        SET msg = CONCAT('Unknown database \'', `tableSchema`, '\'');
        SIGNAL SQLSTATE '42000' SET MESSAGE_TEXT = msg, MYSQL_ERRNO = 1049;
    END IF;

    SET SESSION group_concat_max_len = 10000000;
    SET @rowCounts = (
        SELECT group_concat(CONCAT('SELECT ''',TABLE_NAME,''' AS `table`, COUNT(*) AS `row_count` FROM ', `tableSchema`, '.', TABLE_NAME) SEPARATOR ' union all ')
        FROM information_schema.tables WHERE table_schema = `tableSchema`
        AND TABLE_TYPE = 'BASE TABLE'
    );

    IF @rowCounts IS NOT NULL THEN
        PREPARE statement FROM @rowCounts;
        EXECUTE statement;
        DEALLOCATE PREPARE statement;
    ELSE
        # if no base tables found then return an empty set
        select 1 where 0 = 1;
    END IF;

END$$
DELIMITER ;

Then to use it despite the current database:

CALL admin.database_tables_row_count('my_shop_db');

to get the results.

There is no need to create a separate database just to hold that procedure but I found useful to have a one dedicated database that acts as a kind of toolbox so I don't need to recreate procedures/views/functions for every development and every time after I used drop database...

You may want to change this line:

AND TABLE_TYPE = 'BASE TABLE'

to:

AND TABLE_TYPE IN ('BASE TABLE', 'VIEW')

if you want to get rows count also for the views.

Probably want this if you just want tables and no views:

SELECT TABLE_NAME, TABLE_ROWS
FROM   `information_schema`.`tables` 
WHERE  `table_schema` = 'schema'
	   AND TABLE_TYPE = 'BASE TABLE';

Run this Query a to get results, Information Schema won't give correct results.

Select group_concat(Query SEPARATOR ' union all ') as Full_Query from (SELECT CONCAT('SELECT ''',table_name,''', COUNT(*) FROM ', table_name) as Query
FROM information_schema.tables) AS T1 into @sql from (select 
table_schema db,
table_name tablename from information_schema.tables where table_schema not in 
('performance_schema', 'mysql', 'information_schema')) t; 

Then Run-

prepare s from @sql; execute s; deallocate prepare s;