The query below returns tables in a database (schema) along with the space they use, sorted from the largest use of space to the smallest.

Query

select table_schema as database_name,
    table_name,
    round(sum((data_length + index_length)) / power(1024, 2), 2) as used_mb,
    round(sum((data_length + index_length + data_free)) /
              power(1024, 2), 2) as allocated_mb
from information_schema.tables
where table_schema = 'your database name' -- put your database name here
    and table_type = 'BASE TABLE'
group by table_schema,
         table_name
order by used_mb desc;

Columns

• database_name - table database (schema) name
• table_name - table name
• used_mb - space used in MB
• allocated_mb - space allocated in MB

Rows

• One row: represents one table in a database (schema)
• Scope of rows: all tables in a database (schema)
• Ordered by: size used by the table, from largest to smallest

Sample results

Tables in the Sakila database (schema), sorted from those with more space to the smallest ones.