Get all the rows in one databse. 1

[altendew]

Hi, I know its possible to count all the rows in a table by

SELECT COUNT(*) FROM table

Is there anyway to count all the rows in a database?

Thanks,
Andrew

 

[ClarkKent101]

Hi Altendew,

Try,

SELECT COUNT(*) FROM table1, table2, table3 etc... ;

That's the first method that comes to mind at the moment.

Regards,

- CK

 

[altendew]

Anyone else got any other methods?

Thats a good idea though.. i have other 200 tables though

 

[r937]

actually, that's not a good idea at all, CK

do a search for cross join

after a couple of tables, your count will exceed a bazillion

;-)

andrew, you could run a query to get all the table names, and use the output of that query to generate a whole series of count queries, one per table

r937.com | rudy.ca

 

[Kozusnik]

Even easier. Use phpMyAdmin. Just choose which database you want and it shows total records, total size, and overhead (optimization time).

I know size doesn't matter, but everyone runs out of disk sometime.

Mark

 

[altendew]

Sorry I needed this in a query.

 

[Kozusnik]

Then r937's idea is the best.
Mark

 

[Kozusnik]

I found something...

mysql -u <user> --password=<pass> <database> -e 'show table status;' | awk '{sum=sum+$5;} END {print sum}'

Not quite a sql, but works well from a command line or within a program.

Mark

 

[KarveR]

You want it in a query for use with ?
PHP?
Shell?
Perl?

many ways to skin a cat, some done already.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[altendew]

Well I could create it.. I just didnt know if there is a way to-do it in one query. (PHP)

Thanks.

 

[KarveR]

it takes a few in PHP, but the funtions are there to make it easy.

see
mysql_list_dbs() # returns a list of dbs, loop through each calling mysql_list_tables(db) and loop through and count records.

takes a couple of lines of code but alot more to make it look pretty




______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.