What is the MySQL query to display the number of tables in a database?

Let’s say, here I am using the WEB database. We need to find the number of tables in the database WEB. For this, use the INFORMATION_SCHEMA.TABLES in MySQL.

Following is the query to display the number of tables −

mysql> select count(table_name) as TotalNumberOfTablesInWebDatabase
   -> from information_schema.tables
   -> where table_schema='web';

This will produce the following output −

+----------------------------------+
| TotalNumberOfTablesInWebDatabase |
+----------------------------------+
|                             1562 |
+----------------------------------+
1 row in set (0.27 sec)

To just check whether the count of records displayed above are the same or not, use the SHOW TABLES command. This command would display all the records with the count at the end as shown below−

AmitDiwan

Published on 18-Dec-2019 05:52:25

• Related Questions & Answers
• How to count the number of tables in a MySQL database?
• How to get the size of the tables of a MySQL database?
• Display all tables inside a MySQL database using Java?
• What is a database and what are the advantages of using MySQL database?
• List down all the Tables in a 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?
• How Can I check the size of the tables in a particular MySQL database?
• What is the alias to Show Tables in MySQL Result?
• MySQL query to find the number of rows in the last query
• While connecting to one MySQL database, how can I see the list of tables of other MySQL database?
• What is the default sort order in MySQL tables?
• What is the use of OPTIMIZE TABLE statement in maintaining the MySQL tables?
• What is the use of CHECK TABLE statement in maintaining the MySQL tables?
• Which tables are used to control the privileges of MySQL database server?