size_of_tables.sql

SELECT table_schema                                                                                        AS `Database`,
       table_name                                                                                          AS `Table`,
       FORMAT(table_rows, 'en_US')                                                                         AS `Rows`,
       CONCAT(IF(data_size / POWER(1024, 3) < 1, FORMAT(data_size / POWER(1024, 2), 0),
                 FORMAT(data_size / POWER(1024, 3), 2)), IF(data_size / POWER(1024, 3) < 1, 'MB', 'GB'))   AS `Data`,
       CONCAT(IF(index_size / POWER(1024, 3) < 1, FORMAT(index_size / POWER(1024, 2), 0),
                 FORMAT(index_size / POWER(1024, 3), 2)), IF(index_size / POWER(1024, 3) < 1, 'MB', 'GB')) AS `Indexes`,
       CONCAT(IF(total_size / POWER(1024, 3) < 1, FORMAT(total_size / POWER(1024, 2), 0),
                 FORMAT(total_size / POWER(1024, 3), 2)), IF(total_size / POWER(1024, 3) < 1, 'MB', 'GB')) AS `Total`
FROM (SELECT table_schema,
             table_name,
             data_length                AS data_size,
             index_length               AS index_size,
             data_length + index_length AS `total_size`,
             table_rows
      FROM (SELECT table_schema, table_name, data_length, index_length, table_rows
            FROM information_schema.tables) AS `schemas`
      WHERE ((data_length + index_length) / POWER(1024, 2)) > 1
      ORDER BY table_schema DESC, total_size DESC) AS `table_sizes`;