How to list tables in MySQL or MariaDB

Listing tables in a MySQL or MariaDB database confirms what schema objects exist before running queries, migrations, or backups. It also helps spot missing or unexpected objects after a deployment.

Tables and views live inside a database (schema) on the server, and the server returns their names through SQL statements like SHOW TABLES. The current database context comes from USE <db_name>, and SHOW FULL TABLES adds metadata to distinguish base tables from views. For more control (sorting, filtering, extra columns), querying information_schema.tables returns the same catalog data in a queryable form.

Results depend on privileges and server settings. An account can only see objects it has access to, and an empty list can mean “no privileges” as easily as “no tables.” Pattern matching in SHOW TABLES LIKE uses SQL LIKE wildcards ( and _), and table-name case sensitivity can vary by platform and server configuration.

1. Open a MySQL client session.

   $ mysql -u app -p -h db.example.net
   Enter password: 
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 118
   Server version: 8.0.36 MySQL Community Server - GPL
   
   mysql>

   MariaDB installs often provide the client as mariadb, and the same SQL works in both clients.

2. List databases to identify the target schema name.

   mysql> SHOW DATABASES;
   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | mysql              |
   | performance_schema |
   | sys                |
   | appdb              |
   +--------------------+
   5 rows in set (0.00 sec)

3. Select the target database (schema).

   mysql> USE appdb;
   Database changed

4. Confirm the active database context.

   mysql> SELECT DATABASE();
   +------------+
   | DATABASE() |
   +------------+
   | appdb      |
   +------------+
   1 row in set (0.00 sec)

5. List tables and views in the active database.

   mysql> SHOW TABLES;
   +-------------------+
   | Tables_in_appdb   |
   +-------------------+
   | invoices_view     |
   | order_items       |
   | orders            |
   | products          |
   | user_sessions     |
   | users             |
   +-------------------+
   6 rows in set (0.00 sec)

   An empty result can mean the schema contains no objects or the account lacks privileges to see them.

6. List tables from a specific database without changing the active database.

   mysql> SHOW TABLES FROM mysql;
   +---------------------------+
   | Tables_in_mysql           |
   +---------------------------+
   | columns_priv              |
   | db                        |
   | default_roles             |
   | global_priv               |
   | help_category             |
   ##### snipped #####
   | user                      |
   +---------------------------+
   38 rows in set (0.01 sec)

7. Show table types to distinguish base tables from views.

   mysql> SHOW FULL TABLES;
   +-------------------+------------+
   | Tables_in_appdb   | Table_type |
   +-------------------+------------+
   | invoices_view     | VIEW       |
   | order_items       | BASE TABLE |
   | orders            | BASE TABLE |
   | products          | BASE TABLE |
   | user_sessions     | BASE TABLE |
   | users             | BASE TABLE |
   +-------------------+------------+
   6 rows in set (0.00 sec)

   Wide output can be easier to read in vertical format by ending a statement with \G instead of ;&#44; for example SHOW FULL TABLES\G.</WRAP> - Filter the table list by name pattern. <code>mysql> SHOW TABLES LIKE 'user%'; +-------------------+ | Tables_in_appdb | +-------------------+ | user_sessions | | users | +-------------------+ 2 rows in set (0.00 sec)</code> <WRAP info>SHOW TABLES LIKE uses SQL LIKE wildcards ( and _); a literal underscore can be matched by escaping it (for example 'user\_%'%%).

8. List only base tables.

   mysql> SHOW FULL TABLES WHERE Table_type = 'BASE TABLE';
   +-------------------+------------+
   | Tables_in_appdb   | Table_type |
   +-------------------+------------+
   | order_items       | BASE TABLE |
   | orders            | BASE TABLE |
   | products          | BASE TABLE |
   | user_sessions     | BASE TABLE |
   | users             | BASE TABLE |
   +-------------------+------------+
   5 rows in set (0.00 sec)

9. Query information_schema.tables to sort or filter in SQL.

   mysql> SELECT table_name, table_type
       -> FROM information_schema.tables
       -> WHERE table_schema = DATABASE()
       -> ORDER BY table_name;
   +---------------+------------+
   | table_name    | table_type |
   +---------------+------------+
   | invoices_view | VIEW       |
   | order_items   | BASE TABLE |
   | orders        | BASE TABLE |
   | products      | BASE TABLE |
   | user_sessions | BASE TABLE |
   | users         | BASE TABLE |
   +---------------+------------+
   6 rows in set (0.00 sec)

10. Verify a specific table exists by checking for a single match.

    mysql> SELECT COUNT(*) AS found
        -> FROM information_schema.tables
        -> WHERE table_schema = DATABASE()
        ->   AND table_name = 'orders';
    +-------+
    | found |
    +-------+
    |     1 |
    +-------+
    1 row in set (0.00 sec)

11. List tables non-interactively for scripts.

    $ mysql -u app -p -D appdb -e "SHOW FULL TABLES;"
    Enter password: 
    Tables_in_appdb	Table_type
    invoices_view	VIEW
    order_items	BASE TABLE
    orders	BASE TABLE
    products	BASE TABLE
    user_sessions	BASE TABLE
    users	BASE TABLE