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.
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;
- database_name - table database (schema) name
- table_name - table name
- used_mb - space used in MB
- allocated_mb - space allocated in MB
- 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
Tables in the Sakila database (schema), sorted from those with more space to the smallest ones.