CODEWITHSUNDEEP
phpmysqldatabase
nkspartan
nkspartan

Reputation: 485

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

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?

Upvotes: 1

Views: 1365

Answers (5)

shantanuo
shantanuo

Reputation: 32306

mysqlshow DBName --count

Upvotes: 1

pavium
pavium

Reputation: 15118

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?

Upvotes: 0

RRUZ
RRUZ

Reputation: 136391

try this

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

Upvotes: 8

KV Prajapati
KV Prajapati

Reputation: 94635

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();

Upvotes: 0

TheJacobTaylor
TheJacobTaylor

Reputation: 4143

show tables - for list of tables

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

Upvotes: 3

Related Questions