Methods to List All Tables in a MySQL Database

In this tutorial we will understand 4 different methods to list all tables in a MySQL database with examples for each and detailed explanation. You will easily grasp each one of them and make use of them in future. Let’s start with the first method listing the tables in MySQL.

First Method

The SHOW TABLES Command

When we use “SHOW TABLES” command in a MySQL database it will show result in non TEMPORARY tables and views. It’s a simple command to run. See the example below:

SHOW TABLES;

This will shows us results which will be difficult to differentiate which one is a table or view. So we need to run an other command for that just like that to see “Table_type”:

SHOW FULL TABLES;

We see that to see the full tables we have to type the command.

However, we can return the tables and eliminate views by using command:

SHOW FULL TABLES
WHERE Table_type = 'BASE TABLE';

We can also sort this further by returning the table with names starting with the alphabet “b” to any other condition that we want to try. See the example below:

SHOW TABLES
LIKE 'b%';

Second Method

The SHOW TABLE STATUS Command

We will notice that SHOW TABLE STATUS command is very similar to to the previous SHOW TABLES command, on the contrary it gives us detailed account of ‘non Temporary’ table. We can also use “LIKE’ and “WHERE” clauses if we want to. For Example:

SHOW TABLE STATUS
LIKE 'c%';

Third Method

The information_schema.TABLES Table

We can also query our MySQL database with “information_schema” and we will run “TABLE_TYPE” for type of tables to narrow down our results. See the example below:

SELECT 
    TABLE_NAME
FROM 
    information_schema.TABLES 
WHERE 
    TABLE_SCHEMA LIKE 'School' AND TABLE_TYPE LIKE 'BASE_TABLE';

Fourth Method

The mysqlshow Client

The command “mysqlshow” lets us see the tables. See the example below:

mysqlshow hospital;

You will notice that in this query hospital is our database it show us tables and views. We can also use “sudo” if we cannot “mysqlshow.” See the example below for better understanding.

sudo mysqlshow student;

These are the 4 ways we can use to list all the tables in a MySQL database. Let us know in the comments if you face any trouble.