Skip to content

Instantly share code, notes, and snippets.

@aaronsaderholm

aaronsaderholm/largest_table_by_mb.sql

Created February 11, 2018 01:37
Download ZIP

Revisions

  1. aaronsaderholm created this gist Feb 11, 2018.
    13 changes: 13 additions & 0 deletions largest_table_by_mb.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,13 @@
    -- https://dba.stackexchange.com/a/18952
    SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
    SUBSTR(' KMGTP',pw+1,1),'B') "DataSize",
    CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',
    SUBSTR(' KMGTP',pw+1,1),'B') "IndexSize",
    CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',
    SUBSTR(' KMGTP',pw+1,1),'B') "Total Size"
    FROM (SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,
    SUM(XSize) SXSize,SUM(TSize) STSize
    FROM (SELECT table_schema DB,data_length DSize,index_length XSize,
    data_length+index_length TSize FROM information_schema.tables WHERE
    table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA
    GROUP BY DB WITH ROLLUP) AA,(SELECT 2 pw) BB ORDER BY (SDSize+SXSize);