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 -p
Code 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.