Introduction

There are two different answers to "get record counts for all tables" in MySQL: a fast approximate answer from metadata and an exact answer built from COUNT(*) queries. The right choice depends on whether you need precision or just a quick overview.

Get Approximate Counts from information_schema

For a quick inventory, query information_schema.TABLES:

1SELECT
2    table_name,
3    table_rows
4FROM information_schema.tables
5WHERE table_schema = 'your_database'
6ORDER BY table_name;

This is fast and easy, but table_rows is only an estimate for many storage engines, especially InnoDB. It is useful for rough sizing, not for exact auditing.

Build Exact Counts with Dynamic SQL

If you need exact row counts, you must run COUNT(*) against each table. One common pattern is to generate the SQL dynamically:

1SELECT CONCAT(
2    'SELECT ''',
3    table_name,
4    ''' AS table_name, COUNT(*) AS row_count FROM `',
5    table_name,
6    '`'
7) AS stmt
8FROM information_schema.tables
9WHERE table_schema = 'your_database'
10ORDER BY table_name;

This query returns one SELECT COUNT(*) statement per table. You can then run those generated statements.

In application code or a script, you can loop over the table list and execute each count in turn. That is slower than using metadata, but it produces real counts.

Why Exact Counts Can Be Expensive

COUNT(*) is not free on large transactional tables. Exact counting may require scanning significant data, depending on indexes and storage engine behavior. That means the "correct" query can be much heavier than developers expect.

If the database is large and you only need a dashboard estimate, metadata counts are often the better operational choice. If the result is for billing, reconciliation, or migration validation, exact counts are usually worth the cost.

A Simple Stored-Query Workflow

A practical workflow is:

1. list tables from information_schema
2. generate one COUNT(*) statement per table
3. run them in your SQL client or application
4. collect the results into a report

This separation is often easier to debug than trying to force everything into one giant statement.

Choose Precision Deliberately

The important distinction is:

• metadata counts are fast and approximate
• 'COUNT(*) counts are exact and potentially expensive'

Do not mix those two mental models. Many confusing results come from assuming information_schema.tables.table_rows is an exact count for InnoDB when it is not.

Use Exact Counts Selectively

If you only care about a few critical tables, count those exactly and use metadata for the rest. That hybrid approach is often a better operational compromise than treating every table as equally important.

It also gives you a way to keep expensive counting queries away from the busiest parts of the schema while still validating the tables that matter most.

Common Pitfalls

• Assuming table_rows from information_schema is exact for every table. For InnoDB, it is often only an estimate.
• Running exact counts on a large production database without thinking about cost.
• Forgetting to filter by table_schema, which can return tables from other databases on the same server.
• Generating dynamic SQL without quoting table names properly.
• Using approximate counts for audit or billing logic where exact counts are required.

Summary

• Use information_schema.tables for fast approximate row counts.
• Use COUNT(*) per table when you need exact numbers.
• Exact counts are slower, especially on large transactional tables.
• Filter by the target schema and quote table names correctly.
• Pick the method based on whether the job needs speed or precision.