Skip to content

Instantly share code, notes, and snippets.

@mikhailov

mikhailov/tables_size.sql

Last active August 29, 2015 14:02
Show Gist options
  • Save mikhailov/1a74aa4f243b46a98536 to your computer and use it in GitHub Desktop.
Save mikhailov/1a74aa4f243b46a98536 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
tables_size.sql
SET @db_name = '***';
SELECT
TBname,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(102,pw),3),',',''),17,' '),' ', SUBSTR(' KMGTP',pw,1),'B') "Data Size",
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(102,pw),3),',',''),17,' '),' ', SUBSTR(' KMGTP',pw,1),'B') "Index Size",
CONCAT(ROUND(B.ISize * 100 / B.DSize), ' %') "Percentage",
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(102,pw),3),',',''),17,' '),' ', SUBSTR(' KMGTP',pw,1),'B') "Table Size"
FROM
(SELECT table_name TBname, data_length DSize, index_length ISize, data_length+index_length TSize
FROM information_schema.tables WHERE table_schema = @db_name) B,
(SELECT 3 pw) A ORDER BY ISize DESC, DSize DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment