Mysql / Mariadb - Vista para obtener el número de filas de una tabla y su tamaño en MB

DROP VIEW IF EXISTS view_table_size; CREATE VIEW view_table_size 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_table_size 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 |
| mysql | tables_priv               |      0 |  0.00 |
| mysql | roles_mapping             |      0 |  0.00 |
| mysql | func                      |      0 |  0.00 |
| mysql | column_stats              |      0 |  0.00 |
| mysql | plugin                    |      0 |  0.00 |
| mysql | index_stats               |      0 |  0.00 |
| mysql | gtid_slave_pos            |      0 |  0.02 |
| mysql | event                     |      0 |  0.00 |
| mysql | columns_priv              |      0 |  0.00 |
| mysql | transaction_registry      |      0 |  0.06 |
| mysql | table_stats               |      0 |  0.00 |
| mysql | servers                   |      0 |  0.00 |
| mysql | procs_priv                |      0 |  0.00 |
+-------+---------------------------+--------+-------+

Autor: Eduardo A. F.
Publicado: 24-11-2020 18:38
Actualizado: 24-11-2020 19:21