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.
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:
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%';
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%';
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';
The mysqlshow Client
The command “mysqlshow” lets us see the tables. See the example below:
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.