Get record counts for all tables in MySQL database

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).

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

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

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

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

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

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'