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()