MySQL List/Show Tables

Introduction

Before we discuss MySQL list/show tables, let's first understand-What is MySQL ?

MySQL is an open-source Relational Database Management System (RDBMS) that is widely used for managing and manipulating structured data. In MySQL, the SHOW TABLES statement is used to list all the tables in a database. It provides information about the tables available, allowing users to view the table names and understand the structure of the database.

In this tutorial, you will list or show tables present in the MySQL or MariaDB database via the command line. When we have many tables in the database, the show or list table command is very useful for listing them. We will also address a few FAQs on MySQL list/show tables.

If you want to learn how to list/show databases present in your server, check out the below article:

MySQL Show/List Databases

In this tutorial, you will list or show databases present on the server.

DevOps Tutorials - VegaStackShweta Singh

Show/List MySQL Tables

1) Firstly, you need to connect to the MySQL server using the mysql client to get a list of the tables in the MySQL database.

2) Get access to the MySQL server using the following command:

mysql -u root -p

3) Now, switch to the database using the following command:

USE database_name;

4) To list the tables in this particular database, execute the following command:

SHOW TABLES;

5) You will get the output similar to the below given example:

Output

+----------------------------+
| Tables_in_database_name    |
+----------------------------+
| mall                       |
| shop 1                     |
| shop 2                     |
| shop 3                     |
+----------------------------+
4 rows in set (0.00 sec)

5) Along with the table, if you want to see the table type as well, then use the following command:

SHOW FULL TABLES;

6) You, will get an output like below:

Output

+----------------------------+------------+
| Tables_in_database_name    |Table_type  |
+----------------------------+------------+
| mall                       |VIEW        |
| shop 1                     |BASE TABLE  |
| shop 2                     |BASE TABLE  |
| shop 3                     |BASE TABLE  |
+----------------------------+------------+
4 rows in set (0.00 sec)

7) In case you don't want to switch to the database and still access the tables, use the command like FROM or IN:

SHOW TABLES FROM database_name;

SHOW TABLES IN database_name;

Show Tables Using Pattern Matching

• To filter the output of the SHOW TABLES command according to a particular pattern, use the LIKE command like below:

SHOW TABLES LIKE pattern;

or

SHOW TABLES WHERE expression;

• If you want to get the output matching a specific pattern, use the following command:

mysql> SHOW TABLES IN mysql LIKE "mall%";   

• To list different types of tables, use the WHERE command in the selected database:

SHOW TABLES FROM sakila WHERE table_type= "VIEW";  

Show MySQL Tables from Command Line

You can also get the table details from the Linux shell using the mysql -e to mysqlshow command. This method can come in handy when you want to work with MySQL databases using shell scripts.

1) Use the following command to get a list of all tables in a database:

mysql -u user -p -e 'SHOW TABLES FROM database_name;'

2) You can use mysqlshow command like below:

mysqlshow database_name

Moreover, if you want to filter the output, use the grep command.

FAQs on MySQL List/Show Tables

Conclusion

We hope this detailed tutorial helped you understand how to show or list tables in MySQL or MariaDB Database.

If you have any queries or doubts, please leave them in the comment below. We'll be happy to address them.