List (Show) Tables in a MySQL Database

When managing MySQL
database servers, one of the most frequent tasks you’ll perform is to get familiar with the environment. This includes listing databases
that reside on the server, displaying the database tables, or fetching information about user accounts and their privileges
.

This article shows how to list tables in a MySQL or MariaDB database via the command line.

Show MySQL Tables #

To get a list of the tables in a MySQL database, use the mysql client tool to connect to the MySQL server and run the SHOW TABLES command.

Access the MySQL server:

mysql -u user -p

From within the MySQL shell, switch to the database using the USE statement:

USE database_name;

Execute the following command to get a list of all tables and views in the current database:

SHOW TABLES;

The output will look something like this:

+----------------------------+
| Tables_in_database_name    |
+----------------------------+
| actions                    |
| permissions                |
| permissions_roles          |
| permissions_users          |
| roles                      |
| roles_users                |
| settings                   |
| users                      |
+----------------------------+
8 rows in set (0.00 sec)

The optional FULL modifier will show the table type as a second output column.

SHOW FULL TABLES;

The output will look something like this:

+----------------------------+------------+
| Tables_in_database_name    | Table_type |
+----------------------------+------------+
| actions                    | VIEW       |
| permissions                | BASE TABLE |
| permissions_roles          | BASE TABLE |
| permissions_users          | BASE TABLE |
| roles                      | BASE TABLE |
| roles_users                | BASE TABLE |
| settings                   | BASE TABLE |
| users                      | BASE TABLE |
+----------------------------+------------+

8 rows in set (0.00 sec)

To get a list of the tables without switching to the database, use either the FROM or IN clause followed by the database name:

SHOW TABLES FROM database_name;

The LIKE clause can be used to filter the output of the SHOW TABLES command according to a specific pattern.

SHOW TABLES LIKE pattern;

For example, the following statement will return all databases which names starts with ‘open’:

SHOW TABLES LIKE 'permissions%';

+-------------------------------------------+
| Tables_in_database_name (permissions%)    |
+-------------------------------------------+
| permissions                               |
| permissions_roles                         |
| permissions_users                         |
+-------------------------------------------+
3 rows in set (0.00 sec)

The percent sign (%) means zero, one, or multiple characters.

Show MySQL Tables from the Command Line #

To get information about the tables from the Linux shell, you can use either the mysql -e command or the mysqlshow
command that displays databases and tables information.

This is especially usefully when you want to work with your MySQL databases using shell scripts.

Run the following command on your terminal to show a list of all databases:

mysql -u user -p -e 'SHOW TABLES FROM database_name;'

The output will display a list of all tables:

+----------------------------+
| Tables_in_database_name    |
+----------------------------+
| actions                    |
| permissions                |
| permissions_roles          |
| permissions_users          |
| roles                      |
| roles_users                |
| settings                   |
| users                      |
+----------------------------+

Here is an example using the mysqlshow command:

mysqlshow database_name

You can filter the output with the grep
command.

Conclusion #

To get information about the tables in a MySQL database, use the SHOW TABLES command.

Feel free to leave a comment if you have any questions.