• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 671
  • Last Modified:

mysql query record count

Hi,

Please assist how can we query all the record count for all tables without defining all table names?


Thanks!
0
mikesteven
Asked:
mikesteven
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the statement SHOW TABLE STATUS should be helpful for that:
http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html
0
 
Haris VCommented:

1.exec sp_MSforeachtable N'SELECT "table" = ''?'', cnt = COUNT(*) FROM ?'

2.select object_name(id), rows from sysindexes where indid in (1, 0)

The first query actually counts the rows, and thus takes longer time,
and you get one result set per table. (But you can use INSERT EXEC into
a temp table to avoid this.) One important thing to be aware of is that
sp_MSforeachtable is a undocumented and unsupported function, and could
go up in smoke is MS does not need it themseves any more.

The second query uses cached data in sysindexes, that can be inaccurate. A
DBCC UPDATEUSAGE before you run the query gives better accuracy. This query
executes instantly. DBCC UPDATEUSAGE can take some time, but usually shorter
than performing a SELECT COUNT(*) on tables would.

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sirah: that would be MS SQL Server. the question is for MySQL...
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Also..
select information_schema.TABLES.TABLE_SCHEMA, information_schema.TABLES.TABLE_NAME,information_schema.TABLES.TABLE_ROWS
from information_schema.TABLES
ORDER BY information_schema.TABLES.TABLE_SCHEMA

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now