How to List Tables in MySQL

Galaxy Glossary

How do I list all tables in MySQL?

SHOW TABLES lists all base and view tables in a MySQL database, optionally filtered by pattern or WHERE clause.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

How do I show all tables in MySQL?

Run SHOW TABLES; after connecting to the desired database. MySQL returns every base table and view in that schema.

How can I filter tables by name?

Append LIKE 'pattern%' or a WHERE clause.This limits the result set to tables whose names match the expression, speeding up discovery in large schemas.

Can I list tables in another database?

Qualify the database with FROM db_name or switch databases using USE db_name; before running SHOW TABLES.

When should I use SHOW FULL TABLES?

Use SHOW FULL TABLES to add a Table_type column that tells whether each entry is a BASE TABLE or VIEW.This helps separate logical objects from stored data.

How do I check only base tables?

Combine SHOW FULL TABLES with a WHERE filter: SHOW FULL TABLES WHERE Table_type = 'BASE TABLE';.Views remain hidden.

Best practices for listing tables

Connect to the smallest scope necessary, use pattern filters early, and avoid running SHOW TABLES repeatedly in scripts—cache results when possible.

Example in an ecommerce database

The following snippet lists every table starting with Order in the ecommerce schema so that analysts can quickly locate order-related objects.

SHOW TABLES FROM ecommerce LIKE 'Order%';.

Why How to List Tables in MySQL is important

How to List Tables in MySQL Example Usage


-- List all tables related to orders in the ecommerce database
SHOW TABLES FROM ecommerce LIKE 'Order%';

How to List Tables in MySQL Syntax


SHOW [FULL] TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr];

# Examples
-- 1. Basic
SHOW TABLES;

-- 2. Specific schema
SHOW TABLES FROM ecommerce;

-- 3. Pattern filter
SHOW TABLES LIKE 'Order%';

-- 4. Identify views vs base tables
SHOW FULL TABLES FROM ecommerce WHERE Table_type = 'VIEW';

Common Mistakes

Running SHOW TABLES without selecting or specifying a database. MySQL then returns tables from the default (often unintended) schema. Fix: run USE ecommerce; or add FROM ecommerce.
Using LIKE with leading wildcard (e.g., '%orders%') on large schemas, causing full scans and slow response. Fix: anchor the pattern (e.g., 'orders%') or use WHERE with indexed conditions.

Frequently Asked Questions (FAQs)

Does SHOW TABLES require special privileges?

You need the SHOW DATABASES privilege globally or SELECT privilege on the specific database to view its tables.

Is there an information_schema alternative?

Query information_schema.tables for advanced filtering, joins, or pagination when SHOW TABLES syntax feels limiting.

How can I list temporary tables?

SHOW TABLES ignores session-temporary tables. Instead, query information_schema.innodb_temp_table_info or inspect your session metadata.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie