MySQL Show Table

Last Updated: 15 July, 2023

To show or list all the tables in a MySQL database, we can use the SHOW TABLES command.

Here's an example of how we can use it:

 SHOW TABLES;

When we execute this command, MySQL will return a result set containing the names of all the tables in the currently selected database. Each table name will be listed in a separate row.

If we want to show the tables in a specific database, we can select that database first using the USE statement.

Here's an example:

  mysql> USE school_db;
  Database changed
  mysql> SHOW TABLES;
  +---------------------+
  | Tables_in_school_db |
  +---------------------+
  | admin               |
  | staff               |
  | students            |
  | teachers            |
  | temp_staff          |
  +---------------------+
  4 rows in set (0.00 sec)

Here, we have selected the school_db database, which has four tables, so after running the SHOW TABLES command, it shows all four tables names.

Show Tables from different database without switching the current database

If we want to show or list all the tables from a different database without switching to the current database, we can use either FROM or IN clauses with the SHOW TABLES command. We can see the examples below:

SHOW TABLES with FROM caluse

  mysql> SHOW TABLES FROM school_db;
  +---------------------+
  | Tables_in_school_db |
  +---------------------+
  | admin               |
  | staff               |
  | students            |
  | teachers            |
  | temp_staff          |
  +---------------------+
  5 rows in set (0.00 sec)

SHOW TABLES with IN caluse

  mysql> SHOW TABLES IN school_db;
  +---------------------+
  | Tables_in_school_db |
  +---------------------+
  | admin               |
  | staff               |
  | students            |
  | teachers            |
  | temp_staff          |
  +---------------------+
  5 rows in set (0.00 sec)

Use the 'FULL' modifier with the SHOW TABLES command

To retrieve a list of all tables in a MySQL database, including the table types, we can use the SHOW FULL TABLES statement in the following way:

  mysql> SHOW FULL TABLES;
  +---------------------+------------+
  | Tables_in_school_db | Table_type |
  +---------------------+------------+
  | admin               | BASE TABLE |
  | staff               | BASE TABLE |
  | students            | BASE TABLE |
  | teachers            | BASE TABLE |
  | temp_staff          | VIEW       |
  +---------------------+------------+
  5 rows in set (0.00 sec)

As we can see, there are four tables, and one is a view.

Show Tables Using Pattern Matching

MySQL also allows us to filter the returned tables using the LIKE operator or an expression in the WHERE clause as follows:

Syntax for LIKE operator:

  SHOW TABLES LIKE pattern;

Example:

  mysql> SHOW TABLES LIKE "s%";
  +--------------------------+
  | Tables_in_school_db (s%) |
  +--------------------------+
  | staff                    |
  | students                 |
  +--------------------------+
  2 rows in set (0.10 sec)  

In the above MySLQ query, it shows only those tables that start with the 's' character.

Syntax for WHERE clause:

  SHOW TABLES WHERE expression;

Example:

  mysql> SHOW FULL TABLES WHERE table_type = 'VIEW';
  +---------------------+------------+
  | Tables_in_school_db | Table_type |
  +---------------------+------------+
  | temp_staff          | VIEW       |
  +---------------------+------------+
  1 row in set (0.03 sec)  

In the above MySQL query, we are using the WHERE clause to fetch a list of specific-type tables.

SHOW TABLES statement with an empty database

If we run the SHOW TABLES statement with an empty database or a database that does not have any tables, it will give the below output:

  mysql> CREATE DATABASE college_db;
  Query OK, 1 row affected (0.15 sec)

  mysql> USE college_db;
  Database changed
  
  mysql> SHOW TABLES;
  Empty set (0.03 sec)