Query to find the count of all the tables in a MySQL database

Question

We have been using the following query to find the size (i.e. row count) of all the tables in a database:

SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'my_database_name';

However, we found out today that it does not give the accurate count, but only an approximate count (especially if there is a big disparity in the row count of different tables).

---

Question: What is the correct query to find the number of rows in all the tables of a database?

Answer 1

What I am about to show you is not for the faint of heart.

This will employ dynamic SQL. Here is the code

USE <whatever database>
SET @wid = 15;
SET group_concat_max_len = 1024 * 1024 * 10;
SELECT GROUP_CONCAT(OneSelectQuery SEPARATOR ' UNION ') INTO @BigQuery
FROM
(
    SELECT
        CONCAT('SELECT ',QUOTE(table_name),' tblname,',
        'COUNT(1) RowCount FROM `',table_name,'`') OneSelectQuery
    FROM information_schema.partitions
    WHERE table_schema=DATABASE()
    GROUP BY table_name
) A;
SET @LongQuery = CONCAT('SELECT tblname table_name,LPAD(FORMAT(RowCount,0),@wid,'' '') table_rows FROM (',@BigQuery,') AA');
SELECT @LongQuery\G 
PREPARE qry FROM @LongQuery; EXECUTE qry; DEALLOCATE PREPARE qry;

For this example, I will count the rows for all the tables in the mysql schema

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.34-log |
+------------+
1 row in set (0.00 sec)

mysql> USE mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SET @wid = 15;
Query OK, 0 rows affected (0.00 sec)

mysql> SET group_concat_max_len = 1024 * 1024 * 10;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(OneSelectQuery SEPARATOR ' UNION ') INTO @BigQuery
    -> FROM
    -> (
    ->     SELECT
    ->         CONCAT('SELECT ',QUOTE(table_name),' tblname,',
    ->         'COUNT(1) RowCount FROM `',table_name,'`') OneSelectQuery
    ->     FROM information_schema.partitions
    ->     WHERE table_schema=DATABASE()
    ->     GROUP BY table_name
    -> ) A;
Query OK, 1 row affected (0.02 sec)

mysql> SET @LongQuery = CONCAT('SELECT tblname table_name,LPAD(FORMAT(RowCount,0),@wid,'' '') table_rows FROM (',@BigQuery,') AA');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @LongQuery\G
*************************** 1. row ***************************
@LongQuery: SELECT tblname table_name,LPAD(FORMAT(RowCount,0),@wid,' ') table_rows FROM (SELECT 'columns_priv' tblname,COUNT(1) RowCount FROM `columns_priv` UNION SELECT 'db' tblname,COUNT(1) RowCount FROM `db` UNION SELECT 'engine_cost' tblname,COUNT(1) RowCount FROM `engine_cost` UNION SELECT 'event' tblname,COUNT(1) RowCount FROM `event` UNION SELECT 'func' tblname,COUNT(1) RowCount FROM `func` UNION SELECT 'general_log' tblname,COUNT(1) RowCount FROM `general_log` UNION SELECT 'gtid_executed' tblname,COUNT(1) RowCount FROM `gtid_executed` UNION SELECT 'help_category' tblname,COUNT(1) RowCount FROM `help_category` UNION SELECT 'help_keyword' tblname,COUNT(1) RowCount FROM `help_keyword` UNION SELECT 'help_relation' tblname,COUNT(1) RowCount FROM `help_relation` UNION SELECT 'help_topic' tblname,COUNT(1) RowCount FROM `help_topic` UNION SELECT 'innodb_index_stats' tblname,COUNT(1) RowCount FROM `innodb_index_stats` UNION SELECT 'innodb_table_stats' tblname,COUNT(1) RowCount FROM `innodb_table_stats` UNION SELECT 'ndb_binlog_index' tblname,COUNT(1) RowCount FROM `ndb_binlog_index` UNION SELECT 'plugin' tblname,COUNT(1) RowCount FROM `plugin` UNION SELECT 'proc' tblname,COUNT(1) RowCount FROM `proc` UNION SELECT 'procs_priv' tblname,COUNT(1) RowCount FROM `procs_priv` UNION SELECT 'proxies_priv' tblname,COUNT(1) RowCount FROM `proxies_priv` UNION SELECT 'servers' tblname,COUNT(1) RowCount FROM `servers` UNION SELECT 'server_cost' tblname,COUNT(1) RowCount FROM `server_cost` UNION SELECT 'slave_master_info' tblname,COUNT(1) RowCount FROM `slave_master_info` UNION SELECT 'slave_relay_log_info' tblname,COUNT(1) RowCount FROM `slave_relay_log_info` UNION SELECT 'slave_worker_info' tblname,COUNT(1) RowCount FROM `slave_worker_info` UNION SELECT 'slow_log' tblname,COUNT(1) RowCount FROM `slow_log` UNION SELECT 'tables_priv' tblname,COUNT(1) RowCount FROM `tables_priv` UNION SELECT 'time_zone' tblname,COUNT(1) RowCount FROM `time_zone` UNION SELECT 'time_zone_leap_second' tblname,COUNT(1) RowCount FROM `time_zone_leap_second` UNION SELECT 'time_zone_name' tblname,COUNT(1) RowCount FROM `time_zone_name` UNION SELECT 'time_zone_transition' tblname,COUNT(1) RowCount FROM `time_zone_transition` UNION SELECT 'time_zone_transition_type' tblname,COUNT(1) RowCount FROM `time_zone_transition_type` UNION SELECT 'user' tblname,COUNT(1) RowCount FROM `user`) AA
1 row in set (0.00 sec)

mysql> PREPARE qry FROM @LongQuery; EXECUTE qry; DEALLOCATE PREPARE qry;
Query OK, 0 rows affected (0.03 sec)
Statement prepared

+---------------------------+-----------------+
| table_name                | table_rows      |
+---------------------------+-----------------+
| columns_priv              |               0 |
| db                        |               5 |
| engine_cost               |               2 |
| event                     |               0 |
| func                      |               0 |
| general_log               |               0 |
| gtid_executed             |               0 |
| help_category             |              41 |
| help_keyword              |             699 |
| help_relation             |           1,413 |
| help_topic                |             643 |
| innodb_index_stats        |             576 |
| innodb_table_stats        |              56 |
| ndb_binlog_index          |               0 |
| plugin                    |               0 |
| proc                      |              55 |
| procs_priv                |               0 |
| proxies_priv              |               1 |
| servers                   |               0 |
| server_cost               |               6 |
| slave_master_info         |               0 |
| slave_relay_log_info      |               0 |
| slave_worker_info         |               0 |
| slow_log                  |               0 |
| tables_priv               |               7 |
| time_zone                 |               0 |
| time_zone_leap_second     |               0 |
| time_zone_name            |               0 |
| time_zone_transition      |               0 |
| time_zone_transition_type |               0 |
| user                      |              11 |
+---------------------------+-----------------+
31 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

All you need to do is use the database you wish to count tables on.

Please run this on a replica, never on a busy primary node.