List Tables in a Database Using SHOW TABLES in MySQL

Sometimes, you need to know how many tables are there in the current database, or check whether a specified table exists in the current database.

MySQL provides the SHOW TABLES command to display all tables in a specified database.

MySQL SHOW TABLES syntax

The following is the syntax of MySQL SHOW TABLES command:

SHOW TABLES [FROM database_name] [LIKE pattern];

In this syntax:

The FROM database_name clause indicates the database from which to list tables. It is optional. If not specified, return all tables from the default database.
The LIKE pattern clause is used to filter the results and return a list of eligible tables.

If you do not specify a database as default or do not use FROM clause it in the SHOW TABLES command, MySQL will return an error: ERROR 1046 (3D000): No database selected.

MySQL show table Examples

The following example shows how to list the tables of Sakila sample database.

Connect to the MySQL server using the mysql client tool:
```
mysql -u root -p
```
Enter the password for the root account and press Enter:
```
Enter password: ********
```
Just run the following command to try to list all the tables:
```
SHOW TABLES;
```
At this point, MySQL will return an error: ERROR 1046 (3D000): No database selected. Because you haven’t selected a database as the default database.

Use FROM clause to specify the database to get the table from:

SHOW TABLES FROM sakila;

All tables in the sakila database will be displayed. Here is the output:

+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_copy                 |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
| student                    |
| student_score              |
| subscribers                |
| test                       |
| user                       |
+----------------------------+

Use the USE command to set the default database:
```
USE sakila;
```
Just run the following command to try to list all the tables:
```
SHOW TABLES;
```
At this point, the output of this command is the same as SHOW TABLES FROM sakila;. This is because the default database is sakila now, and we don’t need to specify the database name via FROM clause in the SHOW TABLES.

Return all tables that has a name beginning with a:

SHOW TABLES LIKE 'a%';

+-----------------------+
| Tables_in_sakila (a%) |
+-----------------------+
| actor                 |
| actor_copy            |
| actor_info            |
| address               |
+-----------------------+

This pattern 'a%' will match strings of any length starting with a.

Conclusion

In this article, you learned how to use the SHOW TABLES statement to display tables in a specified database.

MySQL SHOW TABLES syntax

MySQL show table Examples

Conclusion

On this page