Find the size of all databases on a server

A quick way to create a view to show file size statistics across databases on a server.

DROP VIEW IF EXISTS alldb;
CREATE VIEW AllDatabases AS
SELECT
s.schema_name AS 'Schema',
SUM (t.data_length) AS Data,
SUM ( t.index_length ) AS Indexes,
SUM (t.data_length) + SUM (t.index_length) AS 'Mb Used',
IF (SUM(t.data_free)=0,'',SUM (t.data_free)) As 'Mb Free',
IF (SUM(t.data_free)=0,'', 100 * (SUM (t.data_length) + SUM (t.index_length)) / ((SUM (t.data_length)+SUM (t.index_length) + SUM (IFNULL(t.data_free,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
WITH ROLLUP

Leave a Reply

Your email address will not be published. Required fields are marked *