show table name + number of records for each table in a mysql innodb database

How to list all tables in the current database, together the number of rows of the table.

In other words, can you think of a query to come up with something like this in mysql?

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

Different approaches are welcome.

I have a very aggressive approach using brute force Dynamic 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;

Example: In my test database, I get this

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>

GIVE IT A TRY !!!

CAVEAT : If all the tables are MyISAM, this will happen very fast. If all tables are InnoDB, each table will be counted. This may be brutal and unrelenting for very large InnoDB tables.

StackExchange Database Administrators Q#75451, answer score: 9