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
Interview Questions & Answers
Write a query to create a new table which consists of data and structure copied from the other table
What is a primary key in MySQL?
What is a foreign key in MySQL?
What is the difference between CHAR and VARCHAR in MySQL?
What is normalization in MySQL?
What is AUTO_INCREMENT in MySQL?
What is the difference between primary key and unique key?