Get the list of MySQL tables

In addition to data manipulation in DBMS MySQL there is a possibility to work with tables. In particular, working in the MySQL console, you can view the tables in the database at any time. Further we will tell you how to view their list in Ubuntu 20.04 operating system.

Viewing the list of data

Before outputting, you should see which databases are present in DBMS. To output a list of all available databases, run the following command:

SHOW DATABASES;

Get List

When the list of available databases is known, you can view the list in the required database.

How to view tables from a database

Before you can view the list in a database, you need to make (select) it as default. To do this, use the USE command and pass the name of the required database as a parameter. For example, there is a database with the name test_db, then the command will look as follows:

USE test_db;

The output of the message Database changed means that it is selected and all necessary manipulations will now be performed with this database. Now to view all the tables you should execute the command:

SHOW TABLES;

It is easy to view the list of tables in MySQL.

How to solve the task

It is also possible to view tables in other databases while being in any database. As an example, let's choose the test_db database and being in it, execute a query to view tables in another database named users. To do this, you need to execute the command:

SHOW TABLES FROM users;

You can display the list of tables using the FULL option. The optional FULL option displays the table type in a separate column. The type can be VIEW or BASE TABLE. You must execute the command to display this information:

SHOW FULL TABLES;

The LIKE statement can also be used with the SHOW TABLES command to filter the search by pattern. For example, there is test_db and you need to output all tables that start with my. The search template would look like the following:

SHOW TABLES LIKE 'my%';

Viewing without logging into the shell

You can display the list without connecting to the MySQL console. In the example below, you connect under the root user and immediately use the e option to pass a request to display the tables in test_db:

mysql -u root -p -e 'SHOW TABLES FROM test_db;'

This completes the instructions.