List Tables in a Database Using SHOW TABLES in MySQL

Use MySQL SHOW TABLES to list tables and views that your account can access in a database. Specify the database with FROM, or select it first with USE.

If you first need to find a database name, see how to list databases visible to your account.

List tables from the command line

To connect, select a database, and list its tables in one step, run:

mysql -u root -p -D sakila -e "SHOW TABLES;"

Replace root with a MySQL account that can access the database and sakila with its name. The -p option prompts for the password instead of putting it in shell history; -D selects the database and -e executes the SQL statement before the client exits. See the MySQL mysql client options.

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 matching tables.

If you omit FROM and have not selected a default database, MySQL returns Error 1046 (3D000). Use FROM database_name or select a database with USE; see Error 1046 troubleshooting.

MySQL show table Examples

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

1. 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: ********
   

2. 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.

3. 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                       |
   +----------------------------+

4. Use the USE command to set the default database:

   USE sakila;
   

5. 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.

6. 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.

If an expected table is missing

SHOW TABLES lists non-temporary tables and views in one database. MySQL omits a table or view if your account has no privileges on it. Check the database named in FROM or returned by SELECT DATABASE(), then ask an administrator to confirm the object’s privileges if it should be visible. These visibility rules are described in the MySQL SHOW TABLES reference.

If a query that names the table returns Error 1146, check the database and exact table name, then follow the MySQL Error 1146 troubleshooting guide.

Conclusion

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