Is there a way to get the count of rows in all tables in a MySQL database without running a SELECT count()
on each table?
The syntax is as follows. mysql> SELECT SUM(TABLE_ROWS) ->FROM INFORMATION_SCHEMA. TABLES ->WHERE TABLE_SCHEMA = 'business'; The following table returns the count of records.
SELECT TABLE_NAME,SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA. TABLES WHERE TABLE_SCHEMA = 'your_db' GROUP BY TABLE_NAME; That's all you need.
In MySQL the ROW_COUNT() function is used to return the number of rows affected by the previous SQL statement. If the previous statement was not one that could potentially change data rows or you can say, it wasn't an INSERT, UPDATE, DELETE or other such statement this function will return -1.
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).
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**';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With