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?