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 show the tables in a database from the command line, on both MySQL and MariaDB, and how to inspect the structure and contents of any table you find.

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, VIEW for database views, and SYSTEM VIEW for the tables inside information_schema:

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
Max_data_length: 0
   Index_length: 32768
      Data_free: 0
 Auto_increment: 43
    Create_time: 2026-03-14 09:12:07
    Update_time: 2026-08-19 17:44:52
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:

Data_length and Index_length are reported in bytes. For InnoDB, they estimate the space allocated to the clustered index and secondary indexes. The values above represent approximately 16 KB and 32 KB of allocated space, respectively. Treat Rows as an estimate rather than an exact count because InnoDB derives the value from sampled index pages. When you need an exact figure, run SELECT COUNT(*) FROM table_name;.

Show Table Structure and Contents #

Once you know which tables exist, the next question is usually what is inside them. The DESCRIBE statement shows the columns of a table along with their data types, key settings, and defaults:

DESCRIBE users;

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int unsigned | NO   | PRI | NULL    | auto_increment |
| username   | varchar(64)  | NO   | UNI | NULL    |                |
| email      | varchar(255) | NO   | MUL | NULL    |                |
| created_at | datetime     | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

The Key column summarizes how each field participates in indexes. PRI marks a primary-key column, while UNI usually marks the first column of a unique index. MUL means the column can contain repeated values. It commonly marks the first column of a non-unique index, but it can also appear for a column in a composite unique index.

DESC is a shorthand for DESCRIBE, and both are aliases for SHOW COLUMNS FROM, so these three statements return the same output:

DESC users;
SHOW COLUMNS FROM users;
SHOW COLUMNS FROM database_name.users;

The third form is the one to reach for when you want to inspect a table without switching databases first.

To see the exact statement that created a table, including its indexes, storage engine, and character set, use SHOW CREATE TABLE:

SHOW CREATE TABLE users\G

*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL,
  `email` varchar(255) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

This is the output you want when you are copying a table definition to another server or comparing schemas between environments.

Neither statement shows the rows themselves. To display the data stored in a table, run a SELECT query and keep the result small with LIMIT until you know how large the table is:

SELECT * FROM users LIMIT 10;

Running SELECT * without a LIMIT on a large table prints every row to your terminal, so it is worth adding the limit first and raising it once you have seen the shape of the data.

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'
  AND TABLE_TYPE = 'BASE TABLE';

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.

Listing Tables in MariaDB #

MariaDB supports the core table-inspection syntax used throughout this guide, including SHOW TABLES, SHOW FULL TABLES, the LIKE and WHERE filters, SHOW TABLE STATUS, and queries against information_schema.TABLES:

SHOW TABLES FROM database_name;

The results are not identical in every case. MariaDB databases can contain sequences, which appear in SHOW TABLES output and use the SEQUENCE type in SHOW FULL TABLES. The MariaDB client also shows the active database in brackets, while the MySQL client uses a plain mysql> prompt.

In MariaDB 10.5 and later, the client programs are named mariadb and mariadb-show. On Linux, the older mysql and mysqlshow names remain available as symbolic links for compatibility:

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

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' AND TABLE_TYPE = 'BASE TABLE'; from the MySQL shell. The TABLE_TYPE filter prevents views from being included in the count.

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. Use DESCRIBE when you need the columns of a single table, SHOW TABLE STATUS for storage metadata, and information_schema.TABLES when you are scripting or filtering with full SQL. From there you can query data across several tables or manage databases and users from the command line .

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 1000+ Linux tutorials with 20+ years of experience turning complex Linux tasks into clear, reliable guides.

View author page