Get record counts for all tables in MySQL database

sql

database-management

mysql-quirks

sql-queries

byNikita Barsukov·Sep 8, 2024

Looking for a summary of row counts for all tables in your MySQL database? Run this nifty SQL snippet:

SELECT table_name, TABLE_ROWS 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'your_db_name';

This chunk of code pulls a summary directly from INFORMATION_SCHEMA, revealing both the table_name and the estimated TABLE_ROWS for each table in 'your_db_name'.

Obtaining precise row count: The `COUNT(*)` method

While the snippet above gets you there quick, it's an estimate. If you're dealing with InnoDB tables, TABLE_ROWS will give you an approximation, not the precise count. Want exact numbers? Run a COUNT(*) on each table individually:

-- Mt. Countmore is about to erupt. Are you ready for the precise count lava?
SELECT COUNT(*) AS exact_row_count FROM your_table_name;

Adding up all rows: Ultimate row summation with `SUM(TABLE_ROWS)`

Who doesn't like the big totals? Aggregate all your rows across all tables with this:

-- Summon the SUMmoner for the grand total!
SELECT SUM(TABLE_ROWS) 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_db_name';

MySQL quirks: Precision varies

Accuracy can be a tricky beast: different storage engines and versions handle TABLE_ROWS differently. For instance, InnoDB doesn't count rows exactly. And some MySQL versions might throw guesses left and right.

Verification for precision lovers

Want to make sure no TABLE_ROWS estimation is fooling you? Especially when it's mission-critical? Enter the verifier:

-- Welcome to the Verification Station!
SELECT table_name, 
       (SELECT COUNT(*) FROM your_table_name) AS confirmed_row_count
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_db_name';

Organised for efficiency: Sorting by `TABLE_ROWS`

Let's say you want to know the most burgeoning tables fast. Simply modify the query to order by TABLE_ROWS:

-- Time to play "Who's the fattest table?"!
SELECT table_name, TABLE_ROWS 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'your_db_name'
ORDER BY TABLE_ROWS DESC;

Pro moves: Advanced techniques and considerations

After sifting through the basics, let's explore some pro moves and tricks to augment our capabilities.

Playing with multiple databases: Dynamic SQL

Got more than one database to check? Craft dynamic SQL:

-- The SQL Transformer, coming to a theatre near you...
SELECT CONCAT('SELECT ''', table_schema, '.'', table_name, ''' AS table_name, COUNT(*) AS exact_row_count FROM ', table_schema, '.', table_name, ';') 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA IN ('schema1', 'schema2');

This will produce an array of SQL SELECT statements which can then provide row counts from all your desired schemas.

Automation is not absolute

While our dynamic SQL script makes work easier, it merely generates queries and does not execute them. Also, be careful while concatenating the output, as the final UNION may cause syntax errors.