In MySQL, SHOW TABLES is an administrative statement that lists the non-TEMPORARY tables and views in a given database.

SHOW TABLES only lists the tables and views for which you have privileges.

Syntax

The syntax goes like this:

SHOW [EXTENDED] [FULL] TABLES
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

Example

Here’s an example to demonstrate:

SHOW TABLES;

Result:

+-----------------+
| Tables_in_music |
+-----------------+
| Albums          |
| Artists         |
| Genres          |
| valbumsartists  |
| valbumsgenres   |
| vallalbums      |
| vallartists     |
| vallgenres      |
+-----------------+

This shows us the tables in the current database, which in this case is the Music database.

Show the Table Type

We can use the FULL modifier to return the table type:

SHOW FULL TABLES;

Result:

+-----------------+------------+
| Tables_in_music | Table_type |
+-----------------+------------+
| Albums          | BASE TABLE |
| Artists         | BASE TABLE |
| Genres          | BASE TABLE |
| valbumsartists  | VIEW       |
| valbumsgenres   | VIEW       |
| vallalbums      | VIEW       |
| vallartists     | VIEW       |
| vallgenres      | VIEW       |
+-----------------+------------+

We can see that some of the tables returned in this example are actually views.

The LIKE Clause

The LIKE clause can be used to specify which table names to match:

SHOW TABLES
LIKE 'a%';

Result:

+----------------------+
| Tables_in_music (a%) |
+----------------------+
| Albums               |
| Artists              |
+----------------------+

The WHERE Clause

The WHERE clause can be used to filter the results based on a given criteria:

SHOW FULL TABLES
WHERE Table_type = 'BASE TABLE';

Result:

+-----------------+------------+
| Tables_in_music | Table_type |
+-----------------+------------+
| Albums          | BASE TABLE |
| Artists         | BASE TABLE |
| Genres          | BASE TABLE |
+-----------------+------------+

We can also use the WHERE clause against the first column by using the Tables_in_dbname convention, where dbname is the name of the database:

SHOW FULL TABLES
WHERE Tables_in_music = 'Artists';

Result:

+-----------------+------------+
| Tables_in_music | Table_type |
+-----------------+------------+
| Artists         | BASE TABLE |
+-----------------+------------+

The EXTENDED Modifier

The optional EXTENDED modifier can be used to list hidden tables created by failed ALTER TABLE statements. These temporary tables have names beginning with #sql and can be dropped using DROP TABLE.