List of all tables in database, and number of rows in each one?

Question

Does anyone know what MySQL statement I would use to return results containing a list of all the tables in a database and the number of rows in each one?

Answer 1

show tables - for list of tables

show table status - should give you the name, number of rows, and a list of extra info

Answer 2

Use PEAR DB shortcuts methods.

$db=DB::Connect("mysql://root@localhost/testdb");
$tab=$db->getListOf("tables");
....
$db->tableinfo("table_name");
...
$r=$db->query("select * from table_name");
echo $r->numrows();
echo $r->numcols();

Answer 3

try this

SELECT Table_name,Table_rows FROM 
information_schema.tables
WHERE TABLE_SCHEMA = 'yourdatabase'

Answer 4

I'm learning MySQL and didn't know about the show table status command. Nice One!

As I said, I'm just starting, but already my database has over 2 million rows.

Does the host computer have to find storage for all rows if you

select * from table_name

Wouldn't it be simpler to say

select count(*) from table_name

and get only one row in return?

Answer 5

mysqlshow DBName --count