如何列出目前數據庫中的所有表，以及表的行數。

換句話說，你能想出一個查詢來在 mysql 中找到類似的東西嗎？

+------------------------++------------------------+
| Tables_in_database     |  Number of rows         |
+------------------------++------------------------+
| database 1             |   1000                  |
| database 2             |   1500                  |
+------------------------++------------------------+

歡迎不同的方法。

我有一個非常激進的方法，使用蠻力動態 SQL

SET group_concat_max_len = 1024 * 1024 * 100;
SELECT CONCAT('SELECT * FROM (',GROUP_CONCAT(CONCAT('SELECT ',QUOTE(tb),' Tables_in_database,
COUNT(1) "Number of Rows" FROM ',db,'.',tb) SEPARATOR ' UNION '),') A;')
INTO @sql FROM (SELECT table_schema db,table_name tb
FROM information_schema.tables WHERE table_schema = DATABASE()) A;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

範例：在我的測試數據庫中，我得到了這個

mysql> use test
Database changed
mysql> SET group_concat_max_len = 1024 * 1024 * 100;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT('SELECT * FROM (',GROUP_CONCAT(CONCAT('SELECT ',QUOTE(tb),' Tables_in_database,
   '> COUNT(1) "Number of Rows" FROM ',db,'.',tb) SEPARATOR ' UNION '),') A;')
   -> INTO @sql FROM (SELECT table_schema db,table_name tb
   -> FROM information_schema.tables WHERE table_schema = DATABASE()) A;
Query OK, 1 row affected (0.00 sec)

mysql> PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+--------------------+----------------+
| Tables_in_database | Number of Rows |
+--------------------+----------------+
| biblio             |              3 |
| biblio_old         |              7 |
| dep                |              5 |
| e                  |             14 |
| emp                |              4 |
| fruit              |             12 |
| fruit_outoforder   |             12 |
| nums_composite     |              0 |
| nuoji              |              4 |
| prod               |              3 |
| prodcat            |              6 |
| test2              |              9 |
| worktable          |              5 |
| yoshi_scores       |             24 |
+--------------------+----------------+
14 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

試一試 ！！！

CAVEAT ：如果所有表都是 MyISAM，這將很快發生。如果所有表都是 InnoDB，則每個表都會被計算在內。對於非常大的 InnoDB 表來說，這可能是殘酷和無情的。

引用自：https://dba.stackexchange.com/questions/75451