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.

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

How does SHOW TABLES work in MySQL?

When executed, SHOW TABLES retrieves information from the database system catalog and presents a list of table names available within the specified database.

How do I use SHOW TABLES to list tables in a MySQL database?

To list tables in a MySQL database, execute the following command: SHOW TABLES;

Can I use SHOW TABLES to list tables from a specific database?

Yes, you can specify the database name following SHOW TABLES to list tables from a specific database. For example, SHOW TABLES FROM database_name;

Is it possible to filter the output of SHOW TABLES using wildcards or patterns?

Yes, you can filter the output of SHOW TABLES using wildcards or patterns in the statement. For instance, SHOW TABLES LIKE 'prefix%'; lists tables with names starting with "prefix".

Can I see more detailed information about a table using SHOW TABLES?

No, SHOW TABLES only provides a list of table names. To see more detailed information about a specific table, you can use the DESCRIBE or SHOW CREATE TABLE statements.

How can I sort the output of SHOW TABLES?

By default, SHOW TABLES lists tables in the order they were created. However, you can use the ORDER BY clause with appropriate sorting criteria to modify the output order.

Can I list tables from multiple databases using SHOW TABLES?

No, SHOW TABLES only displays tables from the currently selected database. To list tables from multiple databases, you must switch databases and execute SHOW TABLES for each one.

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.