MySQL: Summarize all table row-counts in a single query

Question

When reviewing a database, it's highly useful to get an overview of all the tables, including their row counts:

TableName Count t1 1234 t2 37 ... ...

The MySQL TABLES table in the information_schema database provides a table_rows field:

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

But table_rows is only valid for some database engines, whereas for INNODB it is either NULL or not accurate.

Hence it's necessary to compose a method that does an explicit SELECT Count(*)... for each table.

Across many repetitions of this question on stackoverflow, there are numerous answers that involve a two-step process. One query to create a result set with rows containing the individual select count(*) statements, followed by a textediting procedure to turn that into the actual statement that can produce the desired output.

I had not seen this turned into a single step, so below I post that answer. It's not rocket science, but it's convenient to have it spelled out.

gwideman · Accepted Answer · 2021-10-01 00:46:45Z

The first example code here is a stored procedure which performs the entire process in one step, so far as the user is concerned.

BEGIN # zgwp_tables_rowcounts # TableName RowCount # Outputs a result set listing all tables and their row counts # for the current database SET SESSION group_concat_max_len = 1000000; SET @sql = NULL; SET @dbname = DATABASE(); SELECT GROUP_CONCAT( CONCAT ( 'SELECT ''',table_name,''' as TableName, COUNT(*) as RowCount FROM ', table_name, ' ' ) SEPARATOR 'UNION ' ) AS Qry FROM information_schema.`TABLES` AS t WHERE t.TABLE_SCHEMA = @dbname AND t.TABLE_TYPE = "BASE TABLE" ORDER BY t.TABLE_NAME ASC INTO @sql ; PREPARE stmt FROM @sql; EXECUTE stmt; END

Notes:

The SELECT..INTO @sql creates the necessary query, and the PREPARE... EXECUTE runs it.
Sets the group_concat_max_len variable in order to allow a long enough result string from GROUP_CONCAT.

The above procedure is useful for a quick look in an admin environment like Navicat, or on the command line. However, despite returning a result set, so far as I am aware it can't be referenced in another View or Query, presumably because MySQL is unable to determine, before running it, what result sets it produces, let alone what columns they have.

So, it is still useful to be able to quickly produce, without manual editing, the separate SELECT...UNION statement that can be used as a View. That is useful if you want to join the row counts to some other per-table info from another table. Herewith another stored procedure:

BEGIN # zgwp_tables_rowcounts_view_statement # Output: SelectStatement # Outputs a single row and column, containing a (possibly lengthy) # SELECT...UNION statement that, if used as a View, will output # TableName RowCount for all tables in the current database. SET SESSION group_concat_max_len = 1000000; SET @dbname = DATABASE(); SELECT GROUP_CONCAT( CONCAT ( 'SELECT ''',table_name,''' as TableName, COUNT(*) as RowCount FROM ', table_name, ' ', CHAR(10)) SEPARATOR 'UNION ' ) AS SelectStatement FROM information_schema.`TABLES` AS t WHERE t.TABLE_SCHEMA = @dbname AND t.TABLE_TYPE = "BASE TABLE" ORDER BY t.TABLE_NAME ASC ; END

Notes

Very similar to the first procedure in concept. I added a linebreak (CHAR(10)) to each subsidiary "SELECT...UNION" statement, for convenience in viewing or editing the statement.
You could create this as a function and return the SelectStatement, if that's more convenient for your environment.

Hope that helps.

This is great!. One minor correction would be to add backticks around the second occurrence of table_name in the CONCAT. That makes this work for table names with 'illegal' characters too (I had a table containing -2s in the name for some legacy reason and that broke your solution. Due to formatting limitations here it's a bit hard to provide an example.
@Joep yes I can see how that would be helpful. Thanks for adding to the idea.
How would you limit by specific row count (like 0) or filter by table prefix?

Collectives™ on Stack Overflow

MySQL: Summarize all table row-counts in a single query

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related