Mysql / Mariadb - Vista para obtener el número de filas de una tabla y su tamaño en MB
DROP VIEW IF EXISTS view_tablesize;
CREATE VIEW view_tablesize
AS
SELECT table_schema AS db,
TABLE_NAME AS t,
COALESCE(TABLE_ROWS,0) AS irows,
ROUND(SUM(COALESCE(data_length,0) + COALESCE(index_length,0)) / 1024 / 1024, 2) AS mb
FROM information_schema.TABLES
WHERE 1
-- AND TABLE_SCHEMA=DATABASE()
GROUP BY table_schema, TABLE_NAME
ORDER BY db, t, irows desc, mb DESC;
Ejemplo de uso
SELECT *
FROM view_tablesize
WHERE 1
-- el nombre de tu base de datos
AND db='mysql'
ORDER BY irows desc
+-------+---------------------------+--------+-------+ | db | t | irows | mb | +-------+---------------------------+--------+-------+ | mysql | innodb_index_stats | 532206 | 78.25 | | mysql | time_zone_transition | 122176 | 4.16 | | mysql | innodb_table_stats | 30219 | 4.03 | | mysql | time_zone_transition_type | 8678 | 0.44 | | mysql | time_zone | 1817 | 0.03 | | mysql | time_zone_name | 1817 | 0.39 | | mysql | help_relation | 1028 | 0.03 | | mysql | help_topic | 508 | 0.41 | | mysql | help_keyword | 464 | 0.10 | | mysql | help_category | 39 | 0.00 | | mysql | db | 4 | 0.01 | | mysql | user | 3 | 0.00 | | mysql | general_log | 2 | 0.00 | | mysql | proc | 2 | 0.01 | | mysql | slow_log | 2 | 0.00 | | mysql | proxies_priv | 2 | 0.01 | | mysql | host | 0 | 0.00 | | mysql | time_zone_leap_second | 0 | 0.00 |
Otra version en GB
CREATE VIEW view_tablesize AS
SELECT table_name t
, COALESCE(TABLE_ROWS,0) AS irows
, ROUND(((data_length + index_length) / 1024 / 1024), 2) AS mb
, ROUND(((data_length + index_length) / 1024 / 1024 / 1024), 2) AS gb
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
Autor: Eduardo A. F.
Publicado: 24-11-2020 18:38
Actualizado: 15-04-2021 20:39