MySQL – Count the number of tables in the database
This query counts how many tables a database has. It reads the system table INFORMATION_SCHEMA.TABLES, which keeps the details of every table in the server.
SELECT COUNT(*) AS total_tables
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydatabase';
Change mydatabase to the name of your database. If the database has 5 tables, the result is:
Tutorials Class - Output Window
| total_tables |
|---|
| 5 |
List the table names too
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydatabase'
ORDER BY TABLE_NAME;
Or, if you have already selected the database with USE, the short way is:
SHOW TABLES;
Note:
INFORMATION_SCHEMA.TABLES also lists views. To count only real tables, add AND TABLE_TYPE = 'BASE TABLE' to the WHERE condition.
Related: MySQL SELECT and Aggregate functions.
Learn more about the similar topics:
| Tutorials |
|---|
| MySQL Data Types |
| MySQL CREATE DATABASE |
| MySQL USE and SHOW DATABASES |
| MySQL DROP DATABASE |
| MySQL CREATE TABLE |
| MySQL Constraints |
| MySQL DROP TABLE |
| Exercises & Assignments |
|---|
| Write a query to get all column names of a database table |
| Write a query to create a new table with the data and structure of another table |