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?
A:
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.
phatmanace
2010-06-08 22:49:41
That's SQL Server dynamic SQL syntax - you need to change it to use MySQL's Prepared Statement syntax: http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html
OMG Ponies
2010-06-08 23:18:52
A:
There is in fact. You have to use INFORMATION_SCHEMA. In INFORMATION_SCHEMA.tables there's a column TABLE_ROWS.
kubal5003
2010-06-08 22:50:11
This information is reliable only for some engines. For others, the only way to get the real number of rows is to run `SELECT COUNT(*)`. http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html
a1ex07
2010-06-08 23:01:33