mysql query record count

Posted on 2008-11-18
Last Modified: 2012-08-13

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

Question by:mikesteven
    LVL 142

    Accepted Solution

    the statement SHOW TABLE STATUS should be helpful for that:
    LVL 8

    Expert Comment

    by:Haris V

    1.exec sp_MSforeachtable N'SELECT "table" = ''?'', cnt = COUNT(*) FROM ?' 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.

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    sirah: that would be MS SQL Server. the question is for MySQL...
    LVL 26

    Expert Comment

    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


    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now