How to List All Tables in MySQL

08/06/2021

Contents

In this article, you will learn how to list all tables in MySQL.

Listing all tables in MySQL

In MySQL, a database is made up of one or more tables that contain data. If you need to view a list of all the tables in a MySQL database, you can do so using the SHOW TABLES statement or by querying the information_schema database.

Using the SHOW TABLES statement

The SHOW TABLES statement is used to display a list of all tables in a MySQL database.

Syntax

SHOW TABLES;

Example

SHOW TABLES;

Output:

+------------------------+
| Tables_in_database_name |
+------------------------+
| table1                 |
| table2                 |
| table3                 |
+------------------------+

This statement displays a list of all tables in the current database. The output shows the names of the tables in the database.

Querying the information_schema database

The information_schema database is a system database that contains metadata about all the other databases in MySQL. You can query the information_schema database to view a list of all tables in a specific database.

Syntax

SELECT table_name FROM information_schema.tables WHERE table_schema = 'database_name';

Example

SELECT table_name FROM information_schema.tables WHERE table_schema = 'my_database';

Output:

+----------------+
| table_name     |
+----------------+
| table1         |
| table2         |
| table3         |
+----------------+

This query retrieves the names of all tables in the my_database database by querying the information_schema.tables table. The WHERE clause limits the results to the tables in the specified database.

#MySQL