Whether you need to inventory your database, generate a table list for a script, or check what exists after an import, listing tables in MySQL is a fundamental operation. This guide covers all the methods — from quick interactive commands to scripted exports.

Method 1: SHOW TABLES (Quickest)

The simplest way to list all tables in the current database:

-- First, select the database
USE your_database;

-- List all tables
SHOW TABLES;

Output:

+-------------------------+
| Tables_in_your_database |
+-------------------------+
| users                   |
| orders                  |
| products                |
| categories              |
+-------------------------+
4 rows in set (0.00 sec)

Filter Tables by Pattern

-- List tables starting with "wp_"
SHOW TABLES LIKE 'wp_%';

-- List tables containing "user"
SHOW TABLES LIKE '%user%';

-- List tables ending with "_log"
SHOW TABLES LIKE '%_log';

Show Table Status (with Size and Row Count)

SHOW TABLE STATUS;

This returns detailed information including engine type, row count, data size, and creation date.

For a cleaner output:

SHOW TABLE STATUS \G

Method 2: information_schema (Most Flexible)

The information_schema database contains metadata about all databases and tables:

-- List all tables in a specific database
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'your_database'
ORDER BY table_name;

List Tables with Size Information

SELECT 
    table_name,
    table_rows AS estimated_rows,
    ROUND(data_length / 1024 / 1024, 2) AS data_size_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_size_mb,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_size_mb
FROM information_schema.tables
WHERE table_schema = 'your_database'
ORDER BY (data_length + index_length) DESC;

Output:

+------------+----------------+--------------+---------------+---------------+
| table_name | estimated_rows | data_size_mb | index_size_mb | total_size_mb |
+------------+----------------+--------------+---------------+---------------+
| orders     |         125000 |        45.50 |         12.30 |         57.80 |
| users      |          50000 |        12.00 |          3.50 |         15.50 |
| products   |           5000 |         2.10 |          0.80 |          2.90 |
| categories |            150 |         0.02 |          0.02 |          0.04 |
+------------+----------------+--------------+---------------+---------------+

Note: table_rows is an estimate for InnoDB tables. For exact counts, you need to run SELECT COUNT(*) FROM table_name for each table.

List Tables Across All Databases

SELECT table_schema AS database_name, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
ORDER BY table_schema, table_name;

List Only Views (Not Base Tables)

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_database'
  AND table_type = 'VIEW';

Method 3: Command Line (for Scripts)

List Tables Without Entering the MySQL Shell

mysql -u root -p -e "SHOW TABLES" your_database

Export Table List to a File

# -N removes the column header
mysql -u root -p -N your_database -e "SHOW TABLES" > tables.txt

Export Tables Matching a Pattern

mysql -u root -p -N your_database \
    -e "SELECT table_name FROM information_schema.tables WHERE table_schema = 'your_database' AND table_name LIKE 'wp_%'" \
    > wordpress_tables.txt

Use the Table List in a Script

A common use case is feeding the table list into another operation:

# Export all tables matching a prefix to individual SQL files
for table in $(mysql -u root -p -N your_database -e "SHOW TABLES LIKE 'wp_%'"); do
    mysqldump -u root -p your_database "$table" > "${table}.sql"
    echo "Exported: $table"
done

Generate DROP TABLE Statements

mysql -u root -p -N your_database \
    -e "SELECT CONCAT('DROP TABLE IF EXISTS \`', table_name, '\`;') FROM information_schema.tables WHERE table_schema = 'your_database'" \
    > drop_all_tables.sql

Method 4: SHOW DATABASES (List All Databases)

Before listing tables, you may need to see which databases exist:

-- List all databases
SHOW DATABASES;

-- Or with a filter
SHOW DATABASES LIKE '%prod%';

Method 5: MySQL Workbench

In MySQL Workbench:

  1. Connect to your server
  2. Expand the Schemas panel on the left
  3. Expand your database to see all tables
  4. Right-click a table and select Table Inspector for details

To export a table list: run any of the SQL queries above and use Export from the result grid to save as CSV, JSON, or SQL.

Quick Reference

TaskCommand
List all tablesSHOW TABLES;
Filter by patternSHOW TABLES LIKE 'prefix_%';
Table detailsSHOW TABLE STATUS;
Table sizesSELECT table_name, data_length FROM information_schema.tables WHERE table_schema = 'db';
Export to filemysql -u root -p -N db -e "SHOW TABLES" > tables.txt
List databasesSHOW DATABASES;
List views onlyQuery information_schema.tables WHERE table_type = 'VIEW'

Summary

MySQL provides several ways to list tables: SHOW TABLES for quick interactive use, information_schema.tables for detailed metadata queries, and the mysql command-line client with -e for scripted operations. For most day-to-day work, SHOW TABLES is sufficient. When you need sizes, row counts, or filtered lists for automation, query information_schema.