MySQL SHOW TABLES: List Tables In a MySQL Database

 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:

1. Login to the MySQL database server using a MySQL client such as mysql
2. Switch to a specific database using the statement.
3. Use the command.

The following illustrates the syntax of the MySQL SHOW TABLES command:

SHOW TABLES;

MySQL SHOW TABLES examples

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

Step 1. Connect to the MySQL database server:

>mysql -u root -p
Enter password: **********
mysql>

Step 2. Switch to classicmodels database:

mysql> use classicmodels;
Database changed
mysql>

Step 3. Show tables in the classicmodels database:

> show tables;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| customers               |
| employees               |
| offices                 |
| orderdetails            |
| orders                  |
| payments                |
| productlines            |
| products                |
+-------------------------+
8 rows in set (0.00 sec)

The SHOW TABLES the 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;

Let’s create a view in the classicmodels database called contacts that includes first name, last name, and phone from the employees and customers tables for the demonstration.

CREATE VIEW contacts 
AS 
SELECT lastName, firstName, extension as phone 
FROM employees 
UNION
SELECT contactFirstName, contactLastName, phone 
FROM customers;

Now, you issue the SHOW FULL TABLES command:

> SHOW FULL TABLES
+-------------------------+------------+
| 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)

As you can see, all the tables are the base tables except for the contacts a table which is a view.

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

Fortunately, the SHOW TABLES the 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;

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

> SHOW TABLES LIKE 'p%';
+------------------------------+
| Tables_in_classicmodels (p%) |
+------------------------------+
| payments                     |
| productlines                 |
| products                     |
+------------------------------+
3 rows in set (0.00 sec)

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

> SHOW TABLES LIKE '%es';
+-------------------------------+
| Tables_in_classicmodels (%es) |
+-------------------------------+
| employees                     |
| offices                       |
| productlines                  |
+-------------------------------+
3 rows in set (0.00 sec)

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';
+-------------------------+------------+
| Tables_in_classicmodels | Table_type |
+-------------------------+------------+
| contacts                | VIEW       |
+-------------------------+------------+
1 row in set (0.00 sec)

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%';
+---------------------------+
| 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)

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

SHOW TABLES IN mysql LIKE 'time%';

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.

In this tutorial, you have learned how to use the MySQL SHOW TABLES statement to list all tables in a particular database.