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.

Leave a Reply