List tables by their size in MariaDB database

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 hereand table_type = 'BASE TABLE'group by table_schema,
         table_name
order by used_mb desc;

Columns

1. database_name - table database (schema) name
2. table_name - table name
3. used_mb - space used in MB
4. allocated_mb - space allocated in MB

Rows

1. One row: represents one table in a database (schema)
2. Scope of rows: all tables in a database (schema)
3. 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.

Original blog post- https://dataedo.com/kb/query/mariadb/list-of-tables-by-their-size