Select count(*) from all tables mysql

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

To count the total number of tables, use the concept of count(*) with table_schema. First, to check how many tables are present in our database “business”, we need to use the ‘show’ command.

mysql> show tables;

The following is the output that displays all the tables in the database "business".

+--------------------------+
| Tables_in_business       |
+--------------------------+
| addcheckconstraintdemo   |
| addcolumntable           |
| addconstraintdemo        |
| addnotnulldemo           |
| alphademo                |
| autoincrement            |
| autoincrementtable       |
| backticksymbol           |
| bookindexes              |
| chardemo                 |
| checkdemo                |
| clonestudent             |
| columnexistdemo          |
| columnvaluenulldemo      |
| commaseperatedemo        |
| currentdatetime          |
| dateadddemo              |
| deletedemo               |
| deleterecord             |
| demo                     |
| demo1                    |
| demoascii                |
| demoauto                 |
| demobcrypt               |
| demoemptyandnull         |
| demoint                  |
| demoonreplace            |
| demoschema               |
| demowhere                |
| distcountdemo            |
| distinctdemo             |
| distinctdemo1            |
| duplicatebookindexes     |
| duplicatefound           |
| employeerecords          |
| employeetable            |
| escapedeom               |
| existsrowdemo            |
| findandreplacedemo       |
| firsttable               |
| foreigntable             |
| foreigntabledemo         |
| functionindexdemo        |
| functiontriggersdemo     |
| groupconcatenatedemo     |
| groupdemo                |
| groupdemo1               |
| groupt_concatdemo        |
| ifelsedemo               |
| imagedemo                |
| incasesensdemo           |
| indexingdemo             |
| int1demo                 |
| intdemo                  |
| keydemo                  |
| latandlangdemo           |
| limitoffsetdemo          |
| milliseconddemo          |
| modifycolumnnamedemo     |
| modifydatatype           |
| moneydemo                |
| moviecollection          |
| multipleindexdemo        |
| multiplerecordwithvalues |
| myisamtoinnodbdemo       |
| mytable                  |
| mytable1                 |
| newstudent               |
| nextpreviousdemo         |
| nonasciidemo             |
| nthrecorddemo            |
| nulldemo                 |
| nullwithselect           |
| numbercolumndemo         |
| ondemo                   |
| originaltable            |
| pasthistory              |
| presenthistory           |
| primarytable             |
| primarytable1            |
| primarytabledemo         |
| qutesdemo                |
| rowcountdemo             |
| rownumberdemo            |
| rowstranspose            |
| rowstransposedemo        |
| saveintotextfile         |
| saveoutputintext         |
| secondtable              |
| sequencedemo             |
| singlequotesdemo         |
| smallintdemo             |
| sortingvarchardemo       |
| sourcetable              |
| spacecolumn              |
| student                  |
| studentrecordwithmyisam  |
| studenttable             |
| table1                   |
| table2                   |
| tabledemo                |
| tbldemotrail             |
| tblf                     |
| tblfirst                 |
| tblfunctiontrigger       |
| tblifdemo                |
| tblp                     |
| tblselectdemo            |
| tblstudent               |
| tbluni                   |
| tblupdatelimit           |
| textdemo                 |
| texturl                  |
| timestampdemo            |
| trailingandleadingdemo   |
| transcationdemo          |
| triggedemo               |
| trigger1                 |
| trigger2demo             |
| trimdemo                 |
| trimdemo2                |
| uniqueconstdemo          |
| uniquedemo               |
| unsigneddemo             |
| updtable                 |
| usernameandpassworddemo  |
| varchardemo              |
| varchardemo1             |
| varchardemo2             |
| varcharurl               |
| whereconditon            |
| xmldemo                  |
+--------------------------+
132 rows in set (0.01 sec)

In the above, we have 132 tables in the database business.

To check the count of tables.

mysql> SELECT count(*) AS TOTALNUMBEROFTABLES
   -> FROM INFORMATION_SCHEMA.TABLES
   -> WHERE TABLE_SCHEMA = 'business';

The following output gives the count of all the tables.

+---------------------+
| TOTALNUMBEROFTABLES |
+---------------------+
|                 132 |
+---------------------+
1 row in set (0.01 sec)

Updated on 30-Jul-2019 22:30:23

• Related Questions & Answers
• What is the MySQL query to display the number of tables in a database?
• How to get the size of the tables of a MySQL database?
• Get record count for all tables in MySQL database?
• How to get the list of tables in default MySQL database?
• How to check table status of the tables in a particular MySQL database?
• List down all the Tables in a MySQL Database
• While connecting to one MySQL database, how can I see the list of tables of other MySQL database?
• How Can I check the size of the tables in a particular MySQL database?
• How to count the total number of tables in a page in Selenium with python?
• How do I select four random tables from a MySQL database having thousands of tables?
• Total number of fields in all tables in database?
• How to join tables and fetch values from a MySQL database?
• How can we check the character set of all the tables in a particular MySQL database?
• Get a list of non-empty tables in a particular MySQL database?
• How to count horizontal values on a MySQL database?

How do I count all tables in a database?

How do I find the row count of all tables in a database?

What is count (*) in MySQL?

What is the result of given query count (*) from table?