MySQL SHOW TABLES: List Tables in a Database

When managing MySQL or MariaDB database servers, one of the most common tasks is checking what is inside a database. This includes listing databases that reside on the server, displaying the database tables, or fetching information about user accounts and their privileges .

This guide explains how to list tables in a MySQL or MariaDB database from the command line.

To list visible non-temporary tables and views in the database you are using, run SHOW TABLES; from the MySQL shell. To list tables in another database without switching to it, run SHOW TABLES FROM database_name;.

Show MySQL Tables #

To get a list of tables in a MySQL database, connect to the MySQL server with the mysql client 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 visible non-temporary 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)

SHOW FULL TABLES #

The optional FULL modifier adds a second column showing the table type. The value is BASE TABLE for regular tables and VIEW for database views:

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)

List Tables from a Specific Database #

To list tables without switching to the database first, use the FROM or IN clause followed by the database name:

SHOW TABLES FROM database_name;

Filtering Tables with LIKE #

The LIKE clause filters the output by a name pattern:

SHOW TABLES LIKE 'pattern%';

For example, the following statement returns all tables whose names start with permissions:

SHOW TABLES LIKE 'permissions%';

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

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

Filtering Tables with WHERE #

For more precise filtering, use the WHERE clause. For example, to list only views in the current database:

SHOW FULL TABLES WHERE Table_type = 'VIEW';

+----------------------------+------------+
| Tables_in_database_name    | Table_type |
+----------------------------+------------+
| actions                    | VIEW       |
+----------------------------+------------+
1 row in set (0.00 sec)

Show Table Status #

The SHOW TABLE STATUS command returns detailed metadata for each table, including the storage engine, row count, data size, and creation date:

SHOW TABLE STATUS FROM database_name\G

The \G modifier formats the output vertically, which is easier to read for wide result sets. Here is an example of the output for a single table:

*************************** 1. row ***************************
           Name: users
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 42
 Avg_row_length: 390
    Data_length: 16384
...

Query information_schema.TABLES #

For scripting or more complex queries, you can retrieve table information directly from the information_schema.TABLES system table. This approach gives you the full power of SQL filtering and sorting.

To list all tables in a specific database:

SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database_name'
ORDER BY TABLE_NAME;

To count the number of tables in a database:

SELECT COUNT(*) AS table_count
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database_name';

Show MySQL Tables from the Command Line #

To list tables from the Linux shell without entering the MySQL interactive prompt, use the mysql -e option or the mysqlshow utility.

This is especially useful when you want to work with MySQL in shell scripts.

Run the following command to list visible tables and views in a database:

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

The output will display the visible tables and views:

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

Here is an example using the mysqlshow command:

mysqlshow -u user -p database_name

You can filter the output with the grep command.

Quick Reference #

For a printable quick reference, see the MySQL/MariaDB cheatsheet .

Troubleshooting #

ERROR 1049 (42000): Unknown database
The database name is incorrect or does not exist. Run SHOW DATABASES; first and verify the exact database name.

ERROR 1044 or 1142: Access denied
Your MySQL user does not have permission to view that database or its tables. Connect with a user that has the required privileges, or grant the needed permissions.

SHOW TABLES returns an empty result
Either the database has no tables, you are connected to the wrong database, or your account does not have privileges to see objects. Confirm the active database with SELECT DATABASE();.

FAQ #

What is the difference between BASE TABLE and VIEW in SHOW FULL TABLES?
BASE TABLE is a regular table that stores data. VIEW is a virtual table defined by a stored query. Use SHOW FULL TABLES to see both types along with their classification.

How do I list tables without logging into the MySQL shell?
Run mysql -u user -p -e 'SHOW TABLES FROM database_name;' from your terminal. This executes the query and exits immediately.

How do I count the number of tables in a database?
Run SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'database_name'; from the MySQL shell.

Can I list tables from multiple databases at once?
The SHOW TABLES command works on one database at a time. To list tables across all databases, query information_schema.TABLES without a WHERE TABLE_SCHEMA filter.

Conclusion #

The SHOW TABLES command is the simplest way to list tables in a MySQL or MariaDB database. For detailed metadata, use SHOW TABLE STATUS, and for scripting or advanced filtering, query information_schema.TABLES directly.

About the authors

Dejan Panovski

Dejan Panovski is the founder of Linuxize, an RHCSA-certified Linux system administrator and DevOps engineer based in Skopje, Macedonia. Author of 800+ Linux tutorials with 20+ years of experience turning complex Linux tasks into clear, reliable guides.

View author page