Global Row Count (MySQL) [TSHW Wiki]

Diese Seite ist nicht editierbar. Du kannst den Quelltext sehen, jedoch nicht verändern. Kontaktiere den Administrator, wenn du glaubst, dass hier ein Fehler vorliegt.

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

<code sql>
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
</code>

This code will define a stored procedure, which can then be called like this:

<code sql>
CALL GLOBAL_ROW_COUNT()
</code>