How to Find the Sizes of All Tables in a Database

When dealing with larger databases, it’s often useful to know which tables are actually taking up the space. We can do this with a couple handy SQL commands.

*Note: With the commands shown below, you need to substitute your particular values for @DB_NAME and @TABLE_NAME. Or you could set the values via SQL with the SET command for user-defined variables with SQL.

To find the sizes of ALL tables, ordered largest to smallest, we can run this SQL command:

SELECT 
table_schema as `Database`,
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
WHERE table_schema = "@DB_NAME"
ORDER BY (data_length + index_length) DESC;

To find the size of ONE table in particular, we can run this SQL command:

SELECT
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
WHERE table_schema = "@DB_NAME"
AND table_name = "@TABLE_NAME";