Good evening

I have several dozen tables in my database. They all have the same schema, and I needed to write a query that would give me the amounts of rows per table. Simply two columns: table name and amount of rows in it.

As mentioned here, you can do it via a pretty simple query

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

Two problems here though:

  • It is an approximation (no big deal for me)
  • No way to filter it by a certain column

The latter became important for me at some point.

Constructing and executing the query

Here is the most basic example, taken from here. It simply counts rows in each table and it is probably slower than the one above. Yet you can expand it however you want.

-- Preparing the variable that will hold the query itself
SET @resultQuery = NULL;

-- This has to be set to a number big enough. If you have many tables, increase it.
SET SESSION group_concat_max_len = 65536;

-- The metaquery that creates the query
SELECT
CONCAT(
  GROUP_CONCAT(
    DISTINCT
    CONCAT('SELECT \'',table_name,'\', count(*) as packet_amount FROM ', table_name, '')
    SEPARATOR '\nUNION\n'
  ),
  '\nORDER BY packet_amount DESC;'
  )
INTO
  @resultQuery
FROM
  information_schema.COLUMNS --or TABLES? I don't know, COLUMNS worked for me.
WHERE
  table_schema = 'my_db';

-- Uncomment if you wanna see the query itself
-- SELECT @resultQuery;

-- This is how we run a query from a string stored in a variable
PREPARE stmt from @resultQuery;
EXECUTE stmt;
DEALLOCATE prepare stmt;

See the empty string at the end of the inner CONCAT? You can put the WHERE in there to filter your statements. Or you could get a maximum or average value instead of counting.

Also, notice the group_concat_max_len variable. If you have many tables, GROUP_CONCAT will trim your string to this many characters. By default it is 1024, which is a restrictively small number. So you can increase it on a per-session basis as shown above to bypass this limit. That is why I also added SELECT @resultQuery, just to make sure nothing is trimmed before I run the query.

Wrapping into a procedure

I’m not a big fan of stored procedures. But as long as no critical business logic is built on them (been there. Ouch…), they are fine. We can easily wrap the code above into a procedure

delimiter //

drop procedure if exists all_counts //
create procedure all_counts()
BEGIN

SET @resultQuery = NULL;

SET SESSION group_concat_max_len = 10000000;

SELECT
CONCAT(
  GROUP_CONCAT(
    DISTINCT
    CONCAT('SELECT \'',table_name,'\', count(*) as packet_amount FROM ', table_name, '')
    SEPARATOR '\nUNION\n'
  ),
  '\nORDER BY packet_amount DESC;'
  )
INTO
  @resultQuery
FROM
  information_schema.COLUMNS
WHERE
  table_schema = 'my_db';

prepare stmt from @resultQuery;
execute stmt;
DEALLOCATE prepare stmt;

END //

delimiter ;

And then, all you need to do is CALL all_counts();. One caveat: if the amount of your tables grows rapidly, don’t forget to increase group_concat_max_len. You may need to rerun the above query, it will replace the procedure for you.

In conclusion

Performing a query over an arbitrary amount of tables in a MySQL database can be a little bit unobvious. Even cumbersome. Consider this article a cheatsheet for such situations.

Thanks for tuning in!