How to Show Tables in MySQL
In this tutorial, you'll learn how to show a list of tables in MySQL using the SHOW TABLES statement.
MySQL SHOW TABLES Syntax
The syntax of the SHOW TABLES statement is as follows:
SHOW TABLES
[{FROM | IN} database_name]
[WHERE where_expression | LIKE 'pattern']
Remarks
- Database_name: The name of the database that you want to show a list of tables.
- The WHERE clause or LIKE operator is optional. This optional operator allows you to show a list of tables based on specified conditions.
- You can use either FROM or IN before database_name.
- SHOW TABLES lists the non-temporary tables only.
Examples
Let's create a database named sample_database.
CREATE DATABASE sample_database;
And then, create three tables named customers, suppliers, and employees.
USE sample_database;
CREATE TABLE customers(
id INT NOT NULL AUTO_INCREMENT,
customer_name VARCHAR(100) NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE suppliers(
id INT NOT NULL AUTO_INCREMENT,
supplier_name VARCHAR(100) NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE employees(
id INT NOT NULL AUTO_INCREMENT,
employee_name VARCHAR(100) NOT NULL,
PRIMARY KEY(id)
);
Example 1: Show all tables in the sample_database
Now let's get a list of all tables in the sample_database database that we've just created.
To show all tables in the sample_database, use the following statement:
SHOW TABLES FROM sample_database;
or:
SHOW TABLES IN sample_database;
Tables_in_sample_database |
customers |
employees |
suppliers |
Example 2: Show tables based on the specified conditions
You also can show a list of tables based on the specified conditions.
The following illustrates an example of how to show a list of tables where the table name starts with the c character:
SHOW TABLES IN sample_database LIKE "c%";
Tables_in_sample_database |
customers |
To add more conditions to the SHOW TABLES statement, you need to use a WHERE clause.
Here is an example of using the WHERE clause:
SHOW TABLES IN sample_database WHERE Tables_in_sample_database = "suppliers";
The statement returns the table named suppliers if it exists.
Tables_in_sample_database |
suppliers |
Note that Tables_in_sample_database is a column that holds all table names of a given database. It is the combination of "Tables_in_" and the name of the database that you want to show a list of tables.
You can supply more conditions, as illustrated below.
SHOW TABLES IN sample_database
WHERE Tables_in_sample_database LIKE "c%" OR Tables_in_sample_database LIKE "s%";
Tables_in_sample_database |
customers |
suppliers |
In this tutorial, you've learned how to show a list of tables in a given database using the MySQL SHOW TABLES statement. You also can view a list of tables based on specified conditions using a WHERE clause or LIKE operator.