Stored procedure for reporting database/object storage usage

[Robin Schumacher]

All -

During a webinar I have a couple of days ago, I offered the crowd a quick stored procedure I wrote that mimics SQL Server's sp_spaceused proc. I got such a response for it that I figured I would post it here. Feedback is welcome!

--Robin

DELIMITER $$
DROP PROCEDURE IF EXISTS sp_spaceused
$$
CREATE PROCEDURE sp_spaceused(dbname varchar(30),tabname varchar(30))
BEGIN
/* if dbname is empty string then provide info on all databases */
IF dbname = '' THEN

SELECT a.schema_name database_name,
IFNULL(ROUND((SUM(b.data_length)+SUM(b.index_length))/1024/1024,2),0.00) total_size_mb,
IFNULL(ROUND(((SUM(b.data_length)+SUM(b.index_length))-SUM(b.data_free))/1024/1024,2),0.00) data_used_mb,
IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00) data_free_mb,
IFNULL(ROUND((((SUM(b.data_length)+SUM(b.index_length))-SUM(b.data_free))/((SUM(b.data_length)+SUM(b.index_length)))*100),2),0) pct_used,
COUNT(table_name) total_tables
FROM information_schema.schemata a
LEFT JOIN information_schema.tables b ON a.schema_name = b.table_schema
WHERE a.schema_name != 'information_schema'
GROUP BY a.schema_name
ORDER BY 1;
ELSE
/* check if full database or table requested. If ALL is used for table name, show space for all tables */
CASE tabname
WHEN '' THEN
SELECT a.schema_name database_name,
IFNULL(ROUND((SUM(b.data_length)+SUM(b.index_length))/1024/1024,2),0.00) total_size_mb,
IFNULL(ROUND(((SUM(b.data_length)+SUM(b.index_length))-SUM(b.data_free))/1024/1024,2),0.00) data_used_mb,
IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00) data_free_mb,
IFNULL(ROUND((((SUM(b.data_length)+SUM(b.index_length))-SUM(b.data_free))/((SUM(b.data_length)+SUM(b.index_length)))*100),2),0) pct_used,
COUNT(table_name) total_tables
FROM information_schema.schemata a
LEFT JOIN information_schema.tables b ON a.schema_name = b.table_schema
WHERE a.schema_name = dbname
GROUP BY a.schema_name
ORDER BY 1;
WHEN 'ALL' THEN
SELECT t.table_name table_name,
t.engine,
ROUND((t.data_length + t.index_length)/1024/1024,2) total_size_mb,
ROUND(((t.data_length + t.index_length)- t.data_free)/1024/1024,2) data_used_mb,
ROUND(data_free /1024/1024,2) data_free_mb,
ROUND((((t.data_length + t.index_length )- t.data_free)/(( t.data_length + t.index_length))*100),2) total_pct_used,
ROUND((t.index_length)/1024/1024,2) total_index_size_mb,
t.table_rows
FROM information_schema.tables t
WHERE t.table_schema = dbname
group by t.table_name, t.engine
ORDER BY 1;
ELSE
SELECT t.table_name table_name,
t.engine,
ROUND((t.data_length + t.index_length)/1024/1024,2) total_size_mb,
ROUND(((t.data_length + t.index_length)- t.data_free)/1024/1024,2) data_used_mb,
ROUND(data_free /1024/1024,2) data_free_mb,
ROUND((((t.data_length + t.index_length )- t.data_free)/(( t.data_length + t.index_length))*100),2) total_pct_used,
ROUND((t.index_length)/1024/1024,2) total_index_size_mb,
t.table_rows
FROM information_schema.tables t
WHERE t.table_schema = dbname and
t.table_name = tabname
group by t.table_name, t.engine
ORDER BY 1;
END CASE;

END IF;
END
$$
DELIMITER ;



Edited 1 time(s). Last edit at 09/29/2005 01:52PM by Robin Schumacher.