CODEWITHSUNDEEP
mysql
Alex N.
Alex N.

Reputation: 15935

Select number of rows in all tables in the database

I wonder if this is possible with straight SQL on MySQL. I need to do SELECT COUNT(*) FROM on each table in the database and output results in one result set.

Is it possible to do with just SQL?

Upvotes: 3

Views: 1162

Answers (2)

kubal5003
kubal5003

Reputation: 7254

There is in fact. You have to use INFORMATION_SCHEMA. In INFORMATION_SCHEMA.tables there's a column TABLE_ROWS.

http://dev.mysql.com/doc/refman/5.0/en/tables-table.html

Upvotes: 3

phatmanace
phatmanace

Reputation: 5021

if one SQL counts as a stored proc, then yes!

.. you could do with a cursor and dynamic sql. 

exec("select count(*) from " + @tableName)

....type thing!

I'm sure that mysql probably has a built in SQL or SP that will do this for you, I'm afraid I don't know what it is though.

Upvotes: 0

Related Questions