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