Get record counts for all tables in MySQL database

You can probably put something together with Tables table. I've never done it, but it looks like it has a column for TABLE_ROWS and one for TABLE NAME.

To get rows per table, you can use a query like this:

SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '**YOUR SCHEMA**'; 

Like @Venkatramanan and others I found INFORMATION_SCHEMA.TABLES unreliable (using InnoDB, MySQL 5.1.44), giving different row counts each time I run it even on quiesced tables. Here's a relatively hacky (but flexible/adaptable) way of generating a big SQL statement you can paste into a new query, without installing Ruby gems and stuff.

SELECT CONCAT( 'SELECT "', table_name, '" AS table_name, COUNT(*) AS exact_row_count FROM `', table_schema, '`.`', table_name, '` UNION ' ) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = '**my_schema**'; 

It produces output like this:

SELECT "func" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.func UNION SELECT "general_log" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.general_log UNION SELECT "help_category" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_category UNION SELECT "help_keyword" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_keyword UNION SELECT "help_relation" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_relation UNION SELECT "help_topic" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_topic UNION SELECT "host" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.host UNION SELECT "ndb_binlog_index" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.ndb_binlog_index UNION 

Copy and paste except for the last UNION to get nice output like,

+------------------+-----------------+ | table_name | exact_row_count | +------------------+-----------------+ | func | 0 | | general_log | 0 | | help_category | 37 | | help_keyword | 450 | | help_relation | 990 | | help_topic | 504 | | host | 0 | | ndb_binlog_index | 0 | +------------------+-----------------+ 8 rows in set (0.01 sec) 

I just run:

show table status; 

This will give you the row count for EVERY table plus a bunch of other info. I used to use the selected answer above, but this is much easier.

I'm not sure if this works with all versions, but I'm using 5.5 with InnoDB engine.

Simple way:

SELECT TABLE_NAME, SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{Your_DB}' GROUP BY TABLE_NAME; 

Result example:

+----------------+-----------------+ | TABLE_NAME | SUM(TABLE_ROWS) | +----------------+-----------------+ | calls | 7533 | | courses | 179 | | course_modules | 298 | | departments | 58 | | faculties | 236 | | modules | 169 | | searches | 25423 | | sections | 532 | | universities | 57 | | users | 10293 | +----------------+-----------------+ 

 SELECT TABLE_NAME,SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_db' GROUP BY TABLE_NAME; 

That's all you need.

This stored procedure lists tables, counts records, and produces a total number of records at the end.

To run it after adding this procedure:

CALL `COUNT_ALL_RECORDS_BY_TABLE` (); 

-

The Procedure:

DELIMITER $$ CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `COUNT_ALL_RECORDS_BY_TABLE`() BEGIN DECLARE done INT DEFAULT 0; DECLARE TNAME CHAR(255); DECLARE table_names CURSOR for SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE(); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN table_names; DROP TABLE IF EXISTS TCOUNTS; CREATE TEMPORARY TABLE TCOUNTS ( TABLE_NAME CHAR(255), RECORD_COUNT INT ) ENGINE = MEMORY; WHILE done = 0 DO FETCH NEXT FROM table_names INTO TNAME; IF done = 0 THEN SET @SQL_TXT = CONCAT("INSERT INTO TCOUNTS(SELECT '" , TNAME , "' AS TABLE_NAME, COUNT(*) AS RECORD_COUNT FROM ", TNAME, ")"); PREPARE stmt_name FROM @SQL_TXT; EXECUTE stmt_name; DEALLOCATE PREPARE stmt_name; END IF; END WHILE; CLOSE table_names; SELECT * FROM TCOUNTS; SELECT SUM(RECORD_COUNT) AS TOTAL_DATABASE_RECORD_CT FROM TCOUNTS; END 

There's a bit of a hack/workaround to this estimate problem.

Auto_Increment - for some reason this returns a much more accurate row count for your database if you have auto increment set up on tables.

Found this when exploring why show table info did not match up with the actual data.

SELECT table_schema 'Database', SUM(data_length + index_length) AS 'DBSize', SUM(TABLE_ROWS) AS DBRows, SUM(AUTO_INCREMENT) AS DBAutoIncCount FROM information_schema.tables GROUP BY table_schema; +--------------------+-----------+---------+----------------+ | Database | DBSize | DBRows | DBAutoIncCount | +--------------------+-----------+---------+----------------+ | Core | 35241984 | 76057 | 8341 | | information_schema | 163840 | NULL | NULL | | jspServ | 49152 | 11 | 856 | | mysql | 7069265 | 30023 | 1 | | net_snmp | 47415296 | 95123 | 324 | | performance_schema | 0 | 1395326 | NULL | | sys | 16384 | 6 | NULL | | WebCal | 655360 | 2809 | NULL | | WxObs | 494256128 | 530533 | 3066752 | +--------------------+-----------+---------+----------------+ 9 rows in set (0.40 sec) 

You could then easily use PHP or whatever to return the max of the 2 data columns to give the "best estimate" for row count.

i.e.

SELECT table_schema 'Database', SUM(data_length + index_length) AS 'DBSize', GREATEST(SUM(TABLE_ROWS), SUM(AUTO_INCREMENT)) AS DBRows FROM information_schema.tables GROUP BY table_schema; 

Auto Increment will always be +1 * (table count) rows off, but even with 4,000 tables and 3 million rows, that's 99.9% accurate. Much better than the estimated rows.

The beauty of this is that the row counts returned in performance_schema are erased for you, as well, because greatest does not work on nulls. This may be an issue if you have no tables with auto increment, though.

To see the record counts for the current DB that's in use:

SELECT table_name, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE(); 

One more option: for non InnoDB it uses data from information_schema.TABLES (as it's faster), for InnoDB - select count(*) to get the accurate count. Also it ignores views.

SET @table_schema = DATABASE(); -- or SET @table_schema = 'my_db_name'; SET GROUP_CONCAT_MAX_LEN=131072; SET @selects = NULL; SELECT GROUP_CONCAT( 'SELECT "', table_name,'" as TABLE_NAME, COUNT(*) as TABLE_ROWS FROM `', table_name, '`' SEPARATOR '\nUNION\n') INTO @selects FROM information_schema.TABLES WHERE TABLE_SCHEMA = @table_schema AND ENGINE = 'InnoDB' AND TABLE_TYPE = "BASE TABLE"; SELECT CONCAT_WS('\nUNION\n', CONCAT('SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND ENGINE <> "InnoDB" AND TABLE_TYPE = "BASE TABLE"'), @selects) INTO @selects; PREPARE stmt FROM @selects; EXECUTE stmt USING @table_schema; DEALLOCATE PREPARE stmt; 

If your database has a lot of big InnoDB tables counting all rows can take more time.

If you use the database information_schema, you can use this mysql code (the where part makes the query not show tables that have a null value for rows):

SELECT TABLE_NAME, TABLE_ROWS FROM `TABLES` WHERE `TABLE_ROWS` >=0 

You can try this. It is working fine for me.

SELECT IFNULL(table_schema,'Total') "Database",TableCount FROM (SELECT COUNT(1) TableCount,table_schema FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql') GROUP BY table_schema WITH ROLLUP) A; 

Most other answers suggest using INFORMATION_SCHEMA.TABLES, but in MySQL 8 it no longer exists. Rows count has been moved to INFORMATION_SCHEMA.INNODB_TABLESTATS.

You can query it with:

SELECT * FROM information_schema.INNODB_TABLESTATS WHERE NAME LIKE "YOUR_DB_NAME/%" ORDER BY NUM_ROWS DESC 

Note that it's still an approximation like before, not an exact count.

The following query produces a(nother) query that will get the value of count(*) for every table, from every schema, listed in information_schema.tables. The entire result of the query shown here - all rows taken together - comprise a valid SQL statement ending in a semicolon - no dangling 'union'. The dangling union is avoided by use of a union in the query below.

select concat('select "', table_schema, '.', table_name, '" as `schema.table`, count(*) from ', table_schema, '.', table_name, ' union ') as 'Query Row' from information_schema.tables union select '(select null, null limit 0);'; 

This is what I do to get the actual count (no using the schema)

It's slower but more accurate.

It's a two step process at

1. Get list of tables for your db. You can get it using

   mysql -uroot -p mydb -e "show tables" 

2. Create and assign the list of tables to the array variable in this bash script (separated by a single space just like in the code below)

   array=( table1 table2 table3 ) for i in "${array[@]}" do echo $i mysql -uroot mydb -e "select count(*) from $i" done 

3. Run it:

   chmod +x script.sh; ./script.sh 

Like many others, I have difficulty getting an accurate value on the INFORMATION_SCHEMA tables with InnoDB, and would infinitely benefit from being able to make a query that depends on count(), and, hopefully, do it in one, single query.

First, make sure to enable massive group_concats:

SET SESSION group_concat_max_len = 1000000; 

Then run this query to get the resultant query you'll run for your database.

SELECT CONCAT('SELECT ', GROUP_CONCAT(table1.count SEPARATOR ',\n')) FROM ( SELECT concat('(SELECT count(id) AS \'',table_name,' Count\' ','FROM ',table_name,') AS ',table_name,'_Count') AS 'count' FROM information_schema.tables WHERE table_schema = '**YOUR_DATABASE_HERE**' ) AS table1 

This will generate output such as...

SELECT (SELECT count(id) AS 'table1 Count' FROM table1) AS table1_Count, (SELECT count(id) AS 'table2 Count' FROM table2) AS table2_Count, (SELECT count(id) AS 'table3 Count' FROM table3) AS table3_Count; 

This in turn gave the following results:

*************************** 1. row *************************** table1_Count: 1 table2_Count: 1 table3_Count: 0 

This is how I count TABLES and ALL RECORDS using PHP:

$dtb = mysql_query("SHOW TABLES") or die (mysql_error()); $jmltbl = 0; $jml_record = 0; $jml_record = 0; while ($row = mysql_fetch_array($dtb)) { $sql1 = mysql_query("SELECT * FROM " . $row[0]); $jml_record = mysql_num_rows($sql1); echo "Table: " . $row[0] . ": " . $jml_record record . "<br>"; $jmltbl++; $jml_record += $jml_record; } echo "--------------------------------<br>$jmltbl Tables, $jml_record > records."; 

Poster wanted row counts without counting, but didn't specify which table engine. With InnoDB, I only know one way, which is to count.

This is how I pick my potatoes:

# Put this function in your bash and call with: # rowpicker DBUSER DBPASS DBNAME [TABLEPATTERN] function rowpicker() { UN=$1 PW=$2 DB=$3 if [ ! -z "$4" ]; then PAT="LIKE '$4'" tot=-2 else PAT="" tot=-1 fi for t in `mysql -u "$UN" -p"$PW" "$DB" -e "SHOW TABLES $PAT"`;do if [ $tot -lt 0 ]; then echo "Skipping $t"; let "tot += 1"; else c=`mysql -u "$UN" -p"$PW" "$DB" -e "SELECT count(*) FROM $t"`; c=`echo $c | cut -d " " -f 2`; echo "$t: $c"; let "tot += c"; fi; done; echo "total rows: $tot" } 

I am making no assertions about this other than that this is a really ugly but effective way to get how many rows exist in each table in the database regardless of table engine and without having to have permission to install stored procedures, and without needing to install ruby or php. Yes, its rusty. Yes it counts. count(*) is accurate.

Based on @Nathan's answer above, but without needing to "remove the final union" and with the option to sort the output, I use the following SQL. It generates another SQL statement which then just run:

select CONCAT( 'select * from (\n', group_concat( single_select SEPARATOR ' UNION\n'), '\n ) Q order by Q.exact_row_count desc') as sql_query from ( SELECT CONCAT( 'SELECT "', table_name, '" AS table_name, COUNT(1) AS exact_row_count FROM `', table_schema, '`.`', table_name, '`' ) as single_select FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'YOUR_SCHEMA_NAME' and table_type = 'BASE TABLE' ) Q 

You do need a sufficiently large value of group_concat_max_len server variable but from MariaDb 10.2.4 it should default to 1M.

I don't know why this has to be so hard but that's life. Here's my bash script that performs actual counts. Just save this as (e.g. count_rows.sh ), make it executable (e.g. chmod 755 count_rows.sh ), and run it (e.g. ./count_rows.sh )

#!/bin/bash readarray -t TABLES < <(mysql --skip-column-names -u myuser -pmypassword mydbname -e "show tables") # now we have an array like: # TABLES='([0]="customer" [1]="order" [2]="product")' # You can print out the array with: #declare -p TABLES for i in "${TABLES[@]}" do #echo $i COUNT=$(mysql --skip-column-names -u username -pmypassword mydbname -e "select count(*) from $i") echo $i : $COUNT done 

This is my proposal, pretty clean

SELECT table_name, table_rows FROM information_schema.tables where information_schema.tables.TABLE_SCHEMA="your_schema_name"; 

If you want the exact numbers, use the following ruby script. You need Ruby and RubyGems.

Install following Gems:

$> gem install dbi $> gem install dbd-mysql 

File: count_table_records.rb

require 'rubygems' require 'dbi' db_handler = DBI.connect('DBI:Mysql:database_name:localhost', 'username', 'password') # Collect all Tables sql_1 = db_handler.prepare('SHOW tables;') sql_1.execute tables = sql_1.map { |row| row[0]} sql_1.finish tables.each do |table_name| sql_2 = db_handler.prepare("SELECT count(*) FROM #{table_name};") sql_2.execute sql_2.each do |row| puts "Table #{table_name} has #{row[0]} rows." end sql_2.finish end db_handler.disconnect 

Go back to the command-line:

$> ruby count_table_records.rb 

Output:

Table users has 7328974 rows. 

The code below generation the select query for all tales. Just delete last "UNION ALL" select all result and paste a new query window to run.

SELECT concat('select ''', table_name ,''' as TableName, COUNT(*) as RowCount from ' , table_name , ' UNION ALL ') as TR FROM information_schema.tables where table_schema = 'Database Name'