Getting the table list with sizes in MySQL

We may need to get the table sizes of the tables in a mysql database from the mysql command prompt.

SELECT   TABLE_NAME AS `Table`,   ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 ) AS `Size (KB)` FROM   information_schema.TABLES WHERE   TABLE_SCHEMA = "<database_name>" ORDER BY   (DATA_LENGTH + INDEX_LENGTH) DESC;

Here <database_name> needs to be changed to your database name. Also you can get the sizes in MB or GB by adding /1024 required times. For example, to get the size in MB the code has to be changed as follows;

SELECT   TABLE_NAME AS `Table`,   ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024/1024) AS `Size (MB)` FROM   information_schema.TABLES WHERE   TABLE_SCHEMA = "<database_name>" ORDER BY   (DATA_LENGTH + INDEX_LENGTH) DESC

Good Reference:

https://chartio.com/resources/tutorials/how-to-get-the-size-of-a-table-in-mysql/