Skip to content

Instantly share code, notes, and snippets.

@chrisjimallen

chrisjimallen/usage.sql

Last active October 27, 2015 13:32
Show Gist options
  • Save chrisjimallen/eb831274b6f44e33feaa to your computer and use it in GitHub Desktop.
Save chrisjimallen/eb831274b6f44e33feaa to your computer and use it in GitHub Desktop.
Download ZIP
Show Database Size & Usage
Raw
usage.sql
SELECT s.schema_name,
CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))/1024/1024,2),0.00),"Mb") total_size,
CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),"Mb") data_used,
CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb") data_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) pct_used
FROM INFORMATION_SCHEMA.SCHEMATA s, INFORMATION_SCHEMA.TABLES t
WHERE s.schema_name = t.table_schema
GROUP BY s.schema_name
ORDER BY total_size DESC
@chrisjimallen
Copy link
Author

produces something like this:

screen-shot-2015-10-27-at-13 29 25

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment