Hey guys,
Since yesterday some of you were wondering how much space the DBs were using, i searched the query that our DBAs use, for that information.
See if this is useful for anyone:
(this requires MySQL5)
SELECT
s.schema_name AS 'Schema',
IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length)) /1024/1024,2),0.00) AS 'Total Mb',
IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free
))/1024/1024,2),0.00) AS 'Mb Used',
IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00) AS 'Mb Free',
IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free)) /((SUM(t.data_length)+SUM(t.index_length
)))*100),2),0)
AS 'Pct Used',
COUNT(table_name) AS Tables
FROM INFORMATION_SCHEMA.SCHEMATA s
LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema
GROUP BY s.schema_name;
I also have one for oracle, much more complete...
Thanks,
Paulo
