10

How to list all tables in the current database, together the number of rows of the table.

In other words, can you think of a query to come up with something like this in mysql?

+------------------------++------------------------+
| Tables_in_database     |  Number of rows         |
+------------------------++------------------------+
| database 1             |   1000                  |
| database 2             |   1500                  |
+------------------------++------------------------+

Different approaches are welcome.

  • Are you using MyISAM or InnoDB? Have you looked at this question? – Aaron Bertrand Sep 2 '14 at 1:51
  • @AaronBertrand Thanks, that question is intersting. I use innodb – sjdh Sep 2 '14 at 2:12
8

I have a very aggressive approach using brute force Dynamic SQL

SET group_concat_max_len = 1024 * 1024 * 100;
SELECT CONCAT('SELECT * FROM (',GROUP_CONCAT(CONCAT('SELECT ',QUOTE(tb),' Tables_in_database,
COUNT(1) "Number of Rows" FROM ',db,'.',tb) SEPARATOR ' UNION '),') A;')
INTO @sql FROM (SELECT table_schema db,table_name tb
FROM information_schema.tables WHERE table_schema = DATABASE()) A;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

Example: In my test database, I get this

mysql> use test
Database changed
mysql> SET group_concat_max_len = 1024 * 1024 * 100;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT('SELECT * FROM (',GROUP_CONCAT(CONCAT('SELECT ',QUOTE(tb),' Tables_in_database,
    '> COUNT(1) "Number of Rows" FROM ',db,'.',tb) SEPARATOR ' UNION '),') A;')
    -> INTO @sql FROM (SELECT table_schema db,table_name tb
    -> FROM information_schema.tables WHERE table_schema = DATABASE()) A;
Query OK, 1 row affected (0.00 sec)

mysql> PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+--------------------+----------------+
| Tables_in_database | Number of Rows |
+--------------------+----------------+
| biblio             |              3 |
| biblio_old         |              7 |
| dep                |              5 |
| e                  |             14 |
| emp                |              4 |
| fruit              |             12 |
| fruit_outoforder   |             12 |
| nums_composite     |              0 |
| nuoji              |              4 |
| prod               |              3 |
| prodcat            |              6 |
| test2              |              9 |
| worktable          |              5 |
| yoshi_scores       |             24 |
+--------------------+----------------+
14 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

GIVE IT A TRY !!!

CAVEAT : If all the tables are MyISAM, this will happen very fast. If all tables are InnoDB, each table will be counted. This may be brutal and unrelenting for very large InnoDB tables.

| improve this answer | |
  • This is a great example of dynamic SQL. Thanks! – sjdh Sep 2 '14 at 3:07
  • I tried to replace FROM information_schema.tables by FROM show tables. Can you explain why that does not work? – sjdh Sep 2 '14 at 3:09
  • Wow this is extremely useful!! – Tommy May 10 '15 at 14:21
  • I would love to see a version of this that iterates over all databases. I tried running SELECT DISTINCT table_schema FROM information_schema.tables but couldn't figure out how to wrap your query to run across those results. If I ever figure out the answer, I'll post at dba.stackexchange.com/q/201880/18098 – Ryan Mar 16 at 23:11
  • Actually I found that you already answered at dba.stackexchange.com/a/102284/18098 but there is at least one typo. Even after I fix "DEALLCOATE", I can't get your All Databases version to run. I'm unfamiliar with SELECT @CountSQL\G – Ryan Mar 16 at 23:20
6

Try below query with out dynamic query

SELECT Table_name AS TablesInDatabase ,table_rows AS NumberOfRows 
FROM information_schema.tables 
WHERE Table_schema=DATABASE(); 
| improve this answer | |
  • 1
    This method is only reliable with an all-MyISAM database. InnoDB does not store row counts in any part of its architecture, nor in the INFORMATION_SCHEMA. (See my post dba.stackexchange.com/questions/17926/…). The dynamic approach against INFORMATION_SCHEMA is the only way for InnoDB tables. – RolandoMySQLDBA Sep 2 '14 at 15:27
  • Thank you for sharing this method. I can confirm that this method gives slightly different counts for some of my tables. All my tables are stored in the InnoDB storage engine. – sjdh Sep 4 '14 at 2:06
  • best answer, works like charm. – Pablo Pazos Aug 12 '18 at 1:33
1

Maybe this query can be of help. It shows the data size and number of records.

SET @table=(SELECT DATABASE());
select @table;
SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`,
     table_rows as 'Rows'
FROM information_schema.TABLES 
WHERE table_schema = @table
ORDER BY (data_length + index_length) DESC;
| improve this answer | |
1

Get exact Row Counts for All Tables in MySQL using shell script.

set parameter in parameter.config file as

# Server Details
host_server=10.109.25.37

# Mysql credentials
mysql_user=root
mysql_pass=root
mysql_port=3306

Script to count is:

#!/bin/bash
# This Script is used to take rows count of mysql all user database

# Read Parameter
source parameter.config

# Find path
MY_PATH="`dirname \"$0\"`"
#echo "$MY_PATH"

start=`date +%s`
echo -e "\n\n"
echo MySQL script start runing at Date and Time is: `date +"%D %T"`
echo -e "@@@ Start of rows Count of MySQL on Old Ficus Release $host_server @@@"

echo -e "\n***** MySQL Rows Count Start *****"

#Make directory to save rows count
NOW="$(date +%Y%m%dT%H%M%S)"
dir_name="mysqlRows_$host_server"
if [ ! -d "$MY_PATH/$dir_name" ]; then
    mkdir "$MY_PATH/$dir_name"
fi
echo -e "\n..... Directory $dir_name is Created for mysql....."

echo -e "\n..... Check MySQL Connection ....."
# Verifying mysql connections on new release machine
MYSQL_CONN="-u$mysql_user -p$mysql_pass -h$host_server"
mysql ${MYSQL_CONN} -e "exit"
if [ $? -eq 0 ];
then
    echo -e "\n..... MySQL Database Connection Successful on server $host_server ....."
else
    echo -e "\n..... MySQL Database Connection Fail. Please verify $host_server credential ....."
    exit
fi

echo -e "\nReading MySQL database names..."
mysql ${MYSQL_CONN} -ANe "SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('mysql','information_schema','performance_schema')" > $MY_PATH/$dir_name/dbs_$NOW.txt
DBS="$(cat $MY_PATH/$dir_name/dbs_$NOW.txt)"
# echo -e "\nList of databases:\t" ${DBS}

echo -e "\n..... Running for row count of tables of all user databases ....."

# All User databases
for db in ${DBS[@]}
do
    # echo $db , ${db[@]} 
    # Find list of database
    echo -e "\n\t... Running for ${db[@]} database tables list ..."
    mysql ${MYSQL_CONN} -ANe "SELECT  TABLE_NAME FROM information_schema.TABLES WHERE  TABLE_SCHEMA IN ('${db[@]}')" > $MY_PATH/$dir_name/${db[@]}_tables_$NOW.txt
    TBL="$(cat $MY_PATH/$dir_name/${db[@]}_tables_$NOW.txt)"
    # echo "Table in $db are:" ${TBL[@]}, $MY_PATH/$dir_name/${db[@]}_tables.txt

    echo -e "\n\t... Running for ${db[@]} database tables rows count ..."
    for tbs in ${TBL[@]}
    do
        # echo $tbs , ${tbs[@]}
        count=$(mysql -u$mysql_user -p$mysql_pass -h$host_server ${db[@]} -N -e "select count(*) from ${tbs[@]}")
        # count="$(cat /tmp/$db_rows_$NOW.txt)"
        # echo "Row in $tb Table of $db database are:" ${count[@]}
        echo -e "${db[@]},${tbs[@]},$count" >> $MY_PATH/$dir_name/${db[@]}_rows_$NOW.csv
        echo -e "${db[@]},${tbs[@]},$count" >> $MY_PATH/$dir_name/alldbs_rows_$NOW.csv
    done
done
echo -e "\n..... End of rows count of tables of all databases ....."

echo -e "\n===== MySQL Rows Count End ====="

# Display script execution time.
echo -e "@@@ Completion of Rows Count of MySQL on old Release $host_server @@@"
echo Script ended at Date and Time is: `date +"%D %T"`
end=`date +%s`
runtime=$((end-start))
echo -e "Time(in second) taken for running MySQL row count script:" $runtime "Sec."

save this in file "mysqlrowscount.sh", Run this script using command:

bash mysqlrowscount.sh
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.