MySQL SHOW TABLES

The SHOW TABLES statement lists all the tables that exist inside the currently selected database. It is a quick way to get an overview of a database's structure — especially useful when you first connect to an unfamiliar database or want to verify that a table was created or dropped successfully.

Syntax

SHOW TABLES;

You must have a database selected first (via USE database_name;). If no database is active, MySQL returns an error: No database selected.

Basic Example

USE school;
SHOW TABLES;

Sample output:

The column heading is automatically named Tables_in_database_name.

Listing Tables from Another Database

You can list tables from a specific database without switching to it first, using SHOW TABLES FROM:

SHOW TABLES FROM ecommerce;

This is equivalent to running USE ecommerce; SHOW TABLES; but without changing your active database.

Filtering with LIKE

Use a LIKE pattern to narrow down results when a database has many tables:

-- Tables whose name starts with 'order'
SHOW TABLES LIKE 'order%';

-- Tables whose name contains 'log'
SHOW TABLES LIKE '%log%';

Using information_schema

For more detailed results — such as the table type (BASE TABLE vs VIEW) — query the information_schema.tables metadata table directly:

SELECT table_name, table_type, engine
FROM information_schema.tables
WHERE table_schema = 'school'
ORDER BY table_name;

This returns richer information and can be combined with complex WHERE filters, which is useful in automated scripts or reporting queries.

Key Points to Remember

• SHOW TABLES; lists all tables in the currently active database.
• A database must be selected first with USE — otherwise you get an error.
• Use SHOW TABLES FROM db_name; to list tables from another database without switching.
• Use LIKE patterns to filter results when there are many tables.
• Query information_schema.tables for richer metadata including table type and storage engine.