How to get record counts for all tables in MySQL database - MySQL developer Tutorial

How to get record counts for all tables in MySQL database

If you are interested to get rough idea about Row Count for each of the table, you can use tables from information_schema. That will give you rough estimation of row count.


SELECT Table_name,SUM(table_rows) As RowCount 
     FROM information_schema.tables 
     WHERE TABLE_SCHEMA = 'YourDBName';



To get the actual count, you need to use count(*) function for each of the table. You can generate the query for each of the table with count(*) , once the queries are generated, copy them and run into new query window to get the table with row count.


select Concat('Select "',table_name,
'" as tablename,count(*) from ',table_name,' Union ') as Query from 
 INFORMATION_SCHEMA.TABLES 
WHERE table_schema = 'YourDatabaseName';


Copy the output and remove the UNION from the last line and run rest of statement to get the table names with rowcount.

Determine record count for all the tables in MySQL Database - MySQL Tutorial for Developers

4 comments: