MySQL provides a schema named information_schema. This has a table named TABLES that has detailed information on the available tables, their record count, etc.
The query below helps you with the information on the table_schema, table_name and the record count.
SELECT table_schema, table_name, table_rows FROM information_schema.tables
Output: Truncated output
+---------------------+------------------------------------------------------+------------+
| table_schema | table_name | table_rows |
+---------------------+------------------------------------------------------+------------+
| information_schema | CHARACTER_SETS | NULL |
| information_schema | COLLATIONS | NULL |
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | NULL |
| information_schema | COLUMNS | NULL |
| information_schema | COLUMN_PRIVILEGES | NULL |
| information_schema | ENGINES | NULL |
+---------------------+------------------------------------------------------+------------+
645 rows in set (0.35 sec)