MySQL SHOW TABLES Statement with Examples

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;

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%";

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.

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%";

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.