MySQL database, table name, table size in MB, number of records

I am busy with a big database these days and need to know more about its structure and size. This MySQL query helps you to find a database

  • List of table names
  • Number of rows ()
  • Number of columns

It is a true time saver for me, so I shared it with you.

SELECT `main`.`table_name` AS 'table', `table_rows` AS `rows`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`,`colnum`.`columns`
FROM`information_schema`.`tables` AS `main`
JOIN (SELECT count(*) AS `columns`, `table_name` FROM `information_schema`.`columns` GROUP BY `table_name`) AS `colnum`
ON `main`.`table_name` = `colnum`.`table_name`
WHERE `main`.`table_schema` = 'your_database_name'
AND `main`.`table_type` = 'BASE TABLE'
ORDER BY `table_rows` DESC