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

Updated 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?
• List down all the Tables in a MySQL Database
• How to get the list of tables in default MySQL database?
• What is a database and what are the advantages of using 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?
• 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?
• Which tables are used to control the privileges of MySQL database server?
• What is the alias to Show Tables in MySQL Result?
• How to display all the MySQL tables in one line?
• How to display all the tables in MySQL with a storage engine?
• MySQL Query to find tables modified in the last hour?