Get record counts for all tables in MySQL database
SELECT SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '{your_db}';
Note from the docs though: For InnoDB tables, the row count is only a rough estimate used in SQL optimization. You'll need to use COUNT(*) for exact counts (which is more expensive).
Like @Venkatramanan and others I found INFORMATION_SCHEMA.TABLES unreliable (using InnoDB, MySQL 5.1.44), giving different row counts each time I run it even on quiesced tables. Here's a relatively hacky (but flexible/adaptable) way of generating a big SQL statement you can paste into a new query, without installing Ruby gems and stuff.
SELECT CONCAT(
'SELECT "',
table_name,
'" AS table_name, COUNT(*) AS exact_row_count FROM `',
table_schema,
'`.`',
table_name,
'` UNION '
)
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = '**my_schema**';
It produces output like this:
SELECT "func" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.func UNION
SELECT "general_log" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.general_log UNION
SELECT "help_category" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_category UNION
SELECT "help_keyword" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_keyword UNION
SELECT "help_relation" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_relation UNION
SELECT "help_topic" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_topic UNION
SELECT "host" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.host UNION
SELECT "ndb_binlog_index" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.ndb_binlog_index UNION
Copy and paste except for the last UNION to get nice output like,
+------------------+-----------------+
| table_name | exact_row_count |
+------------------+-----------------+
| func | 0 |
| general_log | 0 |
| help_category | 37 |
| help_keyword | 450 |
| help_relation | 990 |
| help_topic | 504 |
| host | 0 |
| ndb_binlog_index | 0 |
+------------------+-----------------+
8 rows in set (0.01 sec)
You can probably put something together with Tables table. I've never done it, but it looks like it has a column for TABLE_ROWS and one for TABLE NAME.
To get rows per table, you can use a query like this:
SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '**YOUR SCHEMA**';