Find size of all server databases

from the Artful Common Queries page


This is based on a query Mark Leith posted to the MySQL General Discussion list.
DROP VIEW IF EXISTS dbsize;
CREATE VIEW dbsize 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

Return to the Artful Common Queries page