SQL Query to get database & table size in MB/GB

database-and-table-size.sql

SELECT
  'Total size in mysql' as `DB / Table Name`,
  SUM(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2)) AS `SizeInMB`,
  SUM(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024),2)) AS `SizeInGB`
FROM
  information_schema.TABLES

UNION

SELECT
  CONCAT('DB: ', TABLE_SCHEMA) AS `DB`,
  SUM(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2)) AS `SizeInMB`,
  SUM(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024),2)) AS `SizeInGB`
FROM
  information_schema.TABLES
GROUP BY 
  TABLE_SCHEMA
  
UNION

SELECT
  CONCAT('Table: ', TABLE_SCHEMA, '.', TABLE_NAME) as `Table`,
  ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2) AS `SizeInMB`,
  ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024),2) AS `SizeInGB`
FROM
  information_schema.TABLES
ORDER BY
  SizeInMB DESC