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.
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)
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.
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.
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)
That's all, guys. I hope this MySQL article is helpful for you.
Happy Learning... 😀
feedback@javabytechie.com