Global Row Count (MySQL)
Description
This snippet returns an overview of the actual number of rows per table in a MySQL database. This can be useful when using InnoDB, which has inaccurate row counts in its system table.
Usage
DELIMITER $$ CREATE PROCEDURE `GLOBAL_ROW_COUNT`() BEGIN 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
This code will define a stored procedure, which can then be called like this:
CALL GLOBAL_ROW_COUNT()