If you are a database administrator and responsible for managing MySQL server, then one of the most important tasks is to get familiar with the MYSQL environment.

If there are many databases on your server with several tables on each database then show or list the MySQL table is very important. This query can help you to fetch the information about user accounts and their privileges.

In this tutorial, we will show you how to Show or List a table in MySQL.

List or Show MySQL Tables

You can use the SHOW TABLES statement to show or list the MySQL tables from the specified database.

First, you will need to connect to the MySQL console using the following command:

mysql -u root -p

Provide your MySQL root password when prompt then choose the specific database (in this case employeedb) with the following command:

MariaDB [(none)]> USE employeedb;

Next, run the SHOW TABLES command to list or show all the tables:

MariaDB [(none)]> SHOW TABLES;

You should get the following output:


+----------------------+
| Tables_in_employeedb |
+----------------------+
| employee |
| employee1 |
| employee2 |
| employee3 |
| manager1 |
| manager2 |
| user1 |
| user2 |
+----------------------+


You can also use the FULL along with SHOW TABLES commands to get the type of table as shown below:

MariaDB [(none)]> SHOW FULL TABLES;

You should get the following output:


+----------------------+------------+
| Tables_in_employeedb | Table_type |
+----------------------+------------+
| employee | BASE TABLE |
| employee1 | BASE TABLE |
| employee2 | BASE TABLE |
| employee3 | BASE TABLE |
| manager1 | BASE TABLE |
| manager2 | BASE TABLE |
| user1 | BASE TABLE |
| user2 | BASE TABLE |
+----------------------+------------+


MySQL also allows you to list or show the table name from different databases without switching to the database. You can use FROM or IN along with SHOW TABLES statement to list the tables.

MariaDB [(none)]> SHOW TABLES IN employeedb;

Or

MariaDB [(none)]> SHOW TABLES FROM employeedb;

You should see the following screen:

List or Show MySQL Tables From the Command-Line

You can also list the MySQL tables without connecting to the MySQL console. You can use mysqlshow or mysql -e command to print the table or database information.

For example, list all tables from the employeedb database run the following command:

mysql -u root -p -e 'SHOW TABLES FROM employeedb;'

You should see the following output:

You can also use the mysqlshow command to perform the same operation:

mysqlshow employeedb

You should see the following output:

Database: employeedb
+-----------+
| Tables |
+-----------+
| employee |
| employee1 |
| employee2 |
| employee3 |
| manager1 |
| manager2 |
| user1 |
| user2 |
+-----------+


Filter Show Tables with Pattern Matching

You can use LIKE and WHERE clause with Show Tables command to filter the returned table output.

You can use the following syntax to use pattern matching with show table command:

SHOW TABLES LIKE pattern;

Or

SHOW TABLES WHERE expression;

To understand this better let’s take an example.

You have a database name employeedb that contains a lot of tables.

If you want to list the table names starting with “employee”, run the following statement:

MariaDB [(none)]> SHOW TABLES FROM employeedb LIKE "employee%";

You should get the list of all tables starting with letter employee in the following output:


+----------------------------------+
| Tables_in_employeedb (employee%) |
+----------------------------------+
| employee |
| employee1 |
| employee2 |
| employee3 |
+----------------------------------+


The WHERE clause is used to list different types of tables including, View or Base type.

For example, list all tables from the employeedb that are “Base” types, run the following statement:

MariaDB [(none)]> SHOW FULL TABLES FROM employeedb WHERE Table_type="BASE TABLE";

You should get the following output:

+----------------------+------------+
| Tables_in_employeedb | Table_type |
+----------------------+------------+
| employee | BASE TABLE |
| employee1 | BASE TABLE |
| employee2 | BASE TABLE |
| employee3 | BASE TABLE |
| manager1 | BASE TABLE |
| manager2 | BASE TABLE |
| user1 | BASE TABLE |
| user2 | BASE TABLE |
+----------------------+------------+


Conclusion

In this guide, we learned how to Show or List tables in the MySQL database. We have learned how to filter the output using pattern matching. Feel free to ask me if you have any questions.