Get record counts for all tables in MySQL database

Question

Is there a way to get the count of rows in all tables in a MySQL database without running a SELECT count() on each table?

Answer 1

SELECT SUM(TABLE_ROWS) 
     FROM INFORMATION_SCHEMA.TABLES 
     WHERE TABLE_SCHEMA = '{your_db}';

Note from the docs though: For InnoDB tables, the row count is only a rough estimate used in SQL optimization. You'll need to use COUNT(*) for exact counts (which is more expensive).

Answer 2

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**';

Answer 3

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)

Answer 4

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.

Answer 5

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

That's all you need.

Answer 6

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

Answer 7

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 |
+----------------+-----------------+

Answer 8

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.

Answer 9

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.

Answer 10

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;

Answer 11

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

Answer 12

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);';

Answer 13

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
   

Answer 14

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.";

Answer 15

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.

Answer 16

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.

Answer 17

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.

Answer 18

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'

Answer 19

If you know the number of tables and their names, and assuming they each have primary keys, you can use a cross join in combination with COUNT(distinct [column]) to get the rows that come from each table:

SELECT 
   COUNT(distinct t1.id) + 
   COUNT(distinct t2.id) + 
   COUNT(distinct t3.id) AS totalRows
FROM firstTable t1, secondTable t2, thirdTable t3;

Here is an SQL Fiddle example.