MySQL ‘show tables’: How to list tables in a MySQL database?

This is very commonly asked question: How do I show or list all the tables in a MySQL (or MariaDB) database using mysql command line tool?

In brief, to show the tables in a MySQL database, run the command:

show tables;

However before running that command, you will have to login to mysql console. You need to follow the following steps to show the tables in a MySQL database:

1. Log into your database using the mysql from your command line client tool
2. Issue the use command to connect to your targeted database (such as, my_school)
3. Use the MySQL show tables command.

MySQL ‘show tables’: A complete example

Here’s a full explanation. First, you need to connect to your MySQL database using your MySQL client from your OS command line tool:

mysql -u root -p

Next, once you’re logged into your MySQL console, tell MySQL to show all the database if you are not sure the name of the database you want to use:

mysql> show databases; 

It will list databases something like this

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_school          |
| my_shop            |
| my_pets            |
| test               |
+--------------------+

Next, once you’re logged into your MySQL database, ask MySQL which database you want to use:

mysql> use my_school; 

Now type the MySQL show tables command to list the tables in the current database:

mysql> show tables;

For instance, if we type in show tables command in one of my example MySQL databases, I’ll see this output:

mysql> show tables;
+-----------------------+
| Tables_in_my_school   |
+-----------------------+
| crust_sizes           | 
| crust_types           | 
| customers             | 
| orders                | 
| pizza_toppings        | 
| pizzas                | 
| toppings              | 
+-----------------------+
7 rows in set (0.00 sec)

That’s how you show the tables in the MySQL database using the MySQL command line tool in your operating system.

Share on Facebook

Tweet

Follow us

Save