MySQL Show Tables

Last updated: October 19, 2026Oct 19, 2026

Once a database has a few tables, you need a quick way to see what is in it. SHOW TABLES lists the tables (and views) of a database. This tutorial shows the variations of the statement and how to get richer information such as table type, row counts and sizes.

1. Listing Tables in the Current Database

Select a database, then list its tables. The column is named Tables_in_ followed by the database name:

USE retail;
SHOW TABLES;

Result: 6 rows

Note that pune_customers is a view, but SHOW TABLES lists views too.

2. SHOW FULL TABLES: Tables vs Views

Add FULL to see a second column, Table_type:

SHOW FULL TABLES;

Result: 6 rows

Filter to views only (or base tables only) with WHERE:

SHOW FULL TABLES WHERE Table_type = 'VIEW';

Result: 1 row

3. Filtering with LIKE

SHOW TABLES LIKE 'customer%';

Result: 2 rows

SHOW TABLES LIKE '%order%';

Result: 2 rows

4. Listing Tables of Another Database

No need to switch with USE; add FROM (or IN):

SHOW TABLES FROM warehouse;

Result: 2 rows

mysql -u root -p -e "SHOW TABLES FROM warehouse"

5. Errors

SHOW TABLES FROM storeroom;

ERROR 1049 (42000): Unknown database 'storeroom'

Without a selected database, plain SHOW TABLES fails with ERROR 1046 (3D000): No database selected.

6. Using information_schema.tables

For sorting, counting or joining, query the metadata table directly:

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

Result: 6 rows

Count tables per database:

SELECT table_schema, COUNT(*) AS tables
FROM information_schema.tables
WHERE table_schema IN ('retail', 'warehouse') AND table_type = 'BASE TABLE'
GROUP BY table_schema;

Result: 2 rows

7. Exact Row Counts for Every Table

Generate the counting query from metadata, then run it:

SELECT GROUP_CONCAT(
         CONCAT('SELECT ''', table_name, ''' AS tbl, COUNT(*) AS n FROM retail.', table_name)
         SEPARATOR ' UNION ALL ') AS generated_sql
FROM information_schema.tables
WHERE table_schema = 'retail' AND table_type = 'BASE TABLE';

Result: 1 row

SELECT 'customer_addresses' AS tbl, COUNT(*) AS n FROM retail.customer_addresses UNION ALL
SELECT 'customers', COUNT(*) FROM retail.customers UNION ALL
SELECT 'order_items', COUNT(*) FROM retail.order_items UNION ALL
SELECT 'orders', COUNT(*) FROM retail.orders UNION ALL
SELECT 'products', COUNT(*) FROM retail.products;

Result: 5 rows

8. SHOW TABLE STATUS

SHOW TABLE STATUS returns one detailed row per table: engine, row estimate, average row length, data and index size, auto-increment value, collation and more:

SHOW TABLE STATUS LIKE 'orders';

Result: 1 row

9. Listing Tables from Java

try (Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/retail", "root", "your-password");
     ResultSet rs = con.getMetaData().getTables("retail", null, "%", new String[] {"TABLE", "VIEW"})) {
    while (rs.next()) {
        System.out.println(rs.getString("TABLE_NAME") + " - " + rs.getString("TABLE_TYPE"));
    }
}

10. Practice Exercises

SHOW FULL TABLES FROM retail WHERE Table_type = 'BASE TABLE' AND Tables_in_retail LIKE 'order%';

SELECT table_name FROM information_schema.columns
WHERE table_schema = 'retail' AND column_name = 'customer_id';

11. Summary

• SHOW TABLES lists tables and views in the current database.
• SHOW FULL TABLES adds Table_type (BASE TABLE or VIEW).
• Filter with LIKE or WHERE; use FROM db for another database.
• information_schema.tables and SHOW TABLE STATUS give engines, estimates and sizes.
• Use COUNT(*) for exact row counts.