MySQL SHOW TABLES: List Tables In a MySQL Database

Summary: in this tutorial, you will learn how to use the MySQL SHOW TABLES command to query tables in a particular database.

To list tables in a MySQL database, you follow these steps:

First, log in to the MySQL database server using a MySQL client such as mysql
Second, switch to a specific database using the USE statement.
Third, use the SHOW TABLES command.

SHOW TABLES;Code language: SQL (Structured Query Language) (sql)

MySQL SHOW TABLES examples

The following example shows you how to list all the tables in the classicmodels database.

Step 1. Connect to the MySQL database server:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Enter the root’s password and press enter to connect to the MySQL server.

Step 2. Switch to classicmodels database:

use classicmodels;Code language: SQL (Structured Query Language) (sql)

Step 3. Show tables in the classicmodels database:

show tables;

Output:

+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| customers               |
| employees               |
| offices                 |
| orderdetails            |
| orders                  |
| payments                |
| productlines            |
| products                |
+-------------------------+
8 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

SHOW FULL TABLES statement

The SHOW TABLES command allows you to show if a table is a base table or a view. To include the table type in the result, you use the following form of the SHOW TABLES statement.

SHOW FULL TABLES;Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------------+------------+
| Tables_in_classicmodels | Table_type |
+-------------------------+------------+
| customers               | BASE TABLE |
| employees               | BASE TABLE |
| offices                 | BASE TABLE |
| orderdetails            | BASE TABLE |
| orders                  | BASE TABLE |
| payments                | BASE TABLE |
| productlines            | BASE TABLE |
| products                | BASE TABLE |
+-------------------------+------------+
8 rows in set (0.00 sec)Code language: JavaScript (javascript)

Let’s create a view called contacts in the classicmodels database:

CREATE VIEW contacts AS 
SELECT 
  lastName, 
  firstName, 
  extension as phone 
FROM 
  employees 
UNION 
SELECT 
  contactFirstName, 
  contactLastName, 
  phone 
FROM 
  customers;
Code language: SQL (Structured Query Language) (sql)

Now, you run the SHOW FULL TABLES command:

SHOW FULL TABLES

Output:

+-------------------------+------------+
| Tables_in_classicmodels | Table_type |
+-------------------------+------------+
| contacts                | VIEW       |
| customers               | BASE TABLE |
| employees               | BASE TABLE |
| offices                 | BASE TABLE |
| orderdetails            | BASE TABLE |
| orders                  | BASE TABLE |
| payments                | BASE TABLE |
| productlines            | BASE TABLE |
| products                | BASE TABLE |
+-------------------------+------------+
9 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The output indicates that all the tables are the base tables except for the contacts view.

SHOW TABLE LIKE statement

For a database that has many tables, listing all tables at a time may not be intuitive.

Fortunately, the SHOW TABLES command provides you with an option that allows you to filter the returned tables using the LIKE operator or an expression in the WHERE clause as follows:

SHOW TABLES LIKE pattern;

SHOW TABLES WHERE expression;Code language: SQL (Structured Query Language) (sql)

For example, to show all tables in the classicmodels database that starts with the letter p, you use the following statement:

SHOW TABLES LIKE 'p%';Code language: JavaScript (javascript)

Output:

+------------------------------+
| Tables_in_classicmodels (p%) |
+------------------------------+
| payments                     |
| productlines                 |
| products                     |
+------------------------------+
3 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Or to show the tables that end with the string 'es', you use the following statement:

SHOW TABLES LIKE '%es';Code language: JavaScript (javascript)

Output:

+-------------------------------+
| Tables_in_classicmodels (%es) |
+-------------------------------+
| employees                     |
| offices                       |
| productlines                  |
+-------------------------------+
3 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The following statement illustrates how to use the WHERE clause in the SHOW TABLES statement to list all the views in the classicmodels database.

SHOW FULL TABLES WHERE table_type = 'VIEW';Code language: JavaScript (javascript)

Output:

+-------------------------+------------+
| Tables_in_classicmodels | Table_type |
+-------------------------+------------+
| contacts                | VIEW       |
+-------------------------+------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

SHOW TABLES FROM statement

Sometimes, you want to see the tables in the database that you are not connected to. In this case, you can use the FROM clause of the SHOW TABLES statement to specify the database from which you want to show the tables.

The following example demonstrates how to show tables that start with 'time':

SHOW TABLES FROM mysql LIKE 'time%';Code language: JavaScript (javascript)

Output:

+---------------------------+
| Tables_in_mysql (time%)   |
+---------------------------+
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
+---------------------------+
5 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The following statement is equivalent to the statement above but it uses IN instead of FROM.

SHOW TABLES IN mysql LIKE 'time%';Code language: SQL (Structured Query Language) (sql)

It’s important to note that if you don’t have privileges for a base table or view, it won’t show up in the result set of the SHOW TABLES command.

Summary

Use the SHOW TABLE statement to list all tables in a database.
Use the SHOW FULL TABLE statement to return an additional column that indicates the object is a view or table.
Use the SHOW TABLE FROM statement to list tables in a database.
Use the SHOW TABLE WHERE statement or SHOW TABLE LIKE statement to filter the tables in a database.

Was this tutorial helpful?