MySQL clone of sp_spaceused

Following on from yesterdays blog post, a MySQL clone of sp_MsForEachTable, here’s an attempt at a clone of sp_spaceused. There’s a few issues to be aware of involving the storage engine in use. For example the row count is accurate for MyISAM while with InnoDb it seems to be just an estimate. This estimate can vary from execution-to-execution. The procedure derives its information from INFORMATION_SCHEMA.TABLES. To get started create the below procedure in the database you wish to use it in.

DELIMITER $$

DROP PROCEDURE IF EXISTS `usp_mysql_spaceused`$$

CREATE PROCEDURE `usp_mysql_spaceused`(IN var_tablename VARCHAR(255))
    LANGUAGE SQL
    NOT DETERMINISTIC
    COMMENT 'Clone of MSSQL sp_spaceused'
    BEGIN
	# Based on http://msdn.microsoft.com/en-us/library/ms188776.aspx
	IF var_tablename = '' OR var_tablename IS NULL THEN

		-- db info, unallocated_space only reported for InnoDB tables
		SELECT table_schema AS database_name,
		       CONCAT(ROUND(SUM(data_length + index_length + data_free)/ 1024 / 1024, 2), '(MB)') AS database_size,
		       CONCAT(ROUND(SUM(data_free) / 1024 /1024, 2), '(MB)') AS unallocated_space
		FROM information_schema.TABLES
		WHERE table_schema = DATABASE()
		GROUP BY table_schema;

		SELECT CONCAT(ROUND(SUM(data_length + index_length + data_free) / 1024 /1024, 2), '(MB)') AS reserved,
		       CONCAT(ROUND(SUM(data_length) / 1024 /1024, 2), '(MB)') AS data,
		       CONCAT(ROUND(SUM(index_length) / 1024 /1024, 2), '(MB)') AS index_size,
		       CONCAT(ROUND(SUM(data_free) / 1024 /1024, 2), '(MB)') AS unused
		FROM information_schema.TABLES
		WHERE TABLE_SCHEMA = DATABASE();

	ELSE

		-- table info
		-- does the table exist in the current db?
		IF NOT EXISTS (SELECT 1 FROM information_schema.TABLES WHERE TABLE_NAME = var_tablename) THEN

			SELECT 'Table does not exist.' AS error;
		ELSE

			-- rows is estimate based for InnoDb (often a fair bit out)
			-- MyISAM is accurate
			SELECT var_tablename AS `name`,
			       table_rows AS rows,
			       CONCAT(ROUND(SUM(data_length + index_length + data_free) / 1024 /1024, 2), '(MB)') AS reserved,
			       CONCAT(ROUND(SUM(data_length) / 1024 /1024, 2), '(MB)') AS data,
			       CONCAT(ROUND(SUM(index_length) / 1024 /1024, 2), '(MB)') AS index_size,
			       CONCAT(ROUND(SUM(data_free) / 1024 /1024, 2), '(MB)') AS unused -- InnoDb only MySQL 5.1.28
			FROM information_schema.TABLES
			WHERE TABLE_SCHEMA = DATABASE()
			AND TABLE_NAME = var_tablename;

		END IF;

	END IF;

    END$$

DELIMITER ;

Usage is as follows.

Get database size information

To get database level information the procedure should be called with an empty string or NULL.

-- Empty string or null to get db info
CALL usp_mysql_spaceused('');

Two resultset are returned. Similar to the ones described on the sp_spaceused documentation page.

Get table size information

To get information on the size of a table just call the procedure with the appropriate table name.

CALL usp_mysql_spaceused('City');