How to List Tables in MySQL and MariaDB

Create a Linode account to try this guide with a $100 credit.This credit will be applied to any valid services used during your first 60 days.

This guide provides the commands you can use to list tables in MySQL and MariaDB. It also includes instructions on how to list tables using MySQL Workbench.

NOTE: The steps in this guide are written for non-root users. Commands that require elevated privileges are prefixed with sudo. If you’re not familiar with the sudo command, see the Linux Users and Groups guide.

How to Connect to a Remote Database

Check out our guide How to Connect to a MySQL or MariaDB Database for the steps to establish a remote connection to your database via the MySQL command line, or CLI tool.

Refer to our Install MySQL Workbench for Database Administration guide for the steps to install MySQL Workbench and use it to connect to your remote database.

How to List Tables in MySQL or MariaDB

The rest of this guide uses an example database, remote user, and three tables. To follow along, you can set these up yourself by logging into your MySQL or MariaDB server and issuing the commands below. Replace with the IP address of your local machine and password with your own password.

CREATE user 'example_user'@'' IDENTIFIED BY 'password';
GRANT SELECT,INSERT,UPDATE,DELETE ON example_db.* TO 'example_user' IDENTIFIED BY 'password';
USE example_db;
CREATE TABLE message_app_users (user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, user_name TEXT);
CREATE TABLE message_app_channels (channel_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, channel_name TEXT);
CREATE TABLE message_app_messages (message_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, channel_id INT, user_id INT, message_body TEXT, FOREIGN KEY (channel_id) REFERENCES message_app_channels(channel_id), FOREIGN KEY (user_id) REFERENCES message_app_users(user_id));

List tables in MySQL or MariaDB from the Linux Shell

There are two ways to get a particular database's list of tables from the command line. They are as follows:

Use the command like the one below if all you want to do with the database is get its list of tables.

SHOW TABLES FROM example_db;

If, instead, you want to continue to work with the database after listing its tables, use the command below. This command sets the current database and then queries it for the list.

USE example_db;

Both options output a list of tables:

| Tables_in_example_db |
| message_app_channels |
| message_app_messages |
| message_app_users    |

You can also use a single command to connect to the database, fetch the list of tables, and disconnect again. This can be useful if you only want a quick list of tables and do not need to connect to the database otherwise.

To do this, add the -e option to your usual database connection command and follow the option with the MySQL command you want to be executed.

The example below connects to the database as example_user and uses the MySQL command from above to fetch the list of tables in the example_db database. Replace with the IP address of your database server:

mysql -u example_user -p -h -e 'SHOW TABLES FROM example_db;'

List Tables in MySQL or MariaDB Using the MySQL Tool

Open the MySQL Workbench, and select the connection you set up for the database.

If you have not set up the database connection yet, follow the steps in the How to Connect to a Remote Database guide first.

In the query field, enter the following MySQL command:

SHOW TABLES FROM example_db;

Alternatively, you can set the current database first, and then fetch the tables, as in:

USE example_db;

Select the plain lightning icon above the query field, or select Execute (All or Selection) from the Query menu.

The Result Grid shows the output from the command, the desired list of tables:


To learn more about working with MySQL/MariaDB, take a look through our extensive guides. You can find plenty of resources there to solve common database related issues, sharpen your skills, and become more proficient with managing your database.

Create a Linode account to try this guide with a $100 credit!

Great! Next, complete checkout for full access to GeekBits.
Welcome back! You've successfully signed in.
You've successfully subscribed to GeekBits.
Success! Your account is fully activated, you now have access to all content.
Success! Your billing info has been updated.
Your billing was not updated.