MySQL is one of the most famous open-source and freely available DBMS (Database Management Software System). It has an easy-to-use interface and is famous for its speed. If you are doing a job as a database administrator in some big organization, you often need to go through a large number of databases and their tables. So, in this article, we are going to learn how we can list or show tables in the MySQL shell.
In order to get started with listing and showing the tables in a database, login to MySQL shell as a root user by running the following command in the terminal:
Then, select the database by running the USE statement of MySQL:
If you don’t know what databases you have, you can list the databases by running the SHOW DATABASES command of MySQL:
After selecting a database, the simplest and easiest way to list tables is to run the SHOW TABLES statement of MySQL in the shell:
You can see the list of tables in the selected database in the screenshot below.
However, this list only contains the name of the tables. MySQL provides another statement to show the type of the table. Either it is a view or a base table. We can see the table type as well by adding the FULL clause in the SHOW TABLES statement:
As you can see in the screenshot below, we have got the table type as well in the second column, along with the table names.
In MySQL, we can actually list or show tables without selecting a database first. As we did earlier, we do not need to select a database first before listing the tables. We can get the list of tables of any database by running the following command:
Or if you have a long list of tables and you want to filter through them. You can also use the LIKE clause to do so:
To understand the pattern. Suppose we want to list all the tables whose name starts from the ‘tes’. The command for showing the tables will be like this:
The percentage ‘%’ sign denotes that there can be any or no character after that.
Just like how we listed tables without selecting the database first. We can list the tables from a specific database without logging into the MySQL shell as well. In order to accomplish this, run the following command in the terminal:
The ‘-e’ is for executing the MySQL statement.
As you can see in the screenshot, we got the same output or list of tables in the terminal without logging into the MySQL shell and selecting the database.
So, these are some of the ways to show the tables and filter them.
Conclusion
In this article, we have learned how to show the tables in a database in MySQL using different methods. We have also learned how to filter the list of tables using the LIKE clause.
