Introduction
SHOW TABLES in MySQL lists all tables in the current database, but you cannot directly use it in a subquery or SELECT FROM. To filter, search, or programmatically work with table names, query the information_schema.TABLES system table instead. This gives you a standard SQL result set that supports WHERE, JOIN, LIKE, and any other clause — something SHOW TABLES does not allow.
Basic SHOW TABLES Usage
1-- List all tables in the current database
2SHOW TABLES;
3
4-- Filter with LIKE pattern
5SHOW TABLES LIKE 'user%';
6-- +------------------------+
7-- | Tables_in_mydb (user%) |
8-- +------------------------+
9-- | users |
10-- | user_profiles |
11-- | user_sessions |
12-- +------------------------+
13
14-- Show tables from a specific database
15SHOW TABLES FROM other_database;
Why You Cannot SELECT FROM SHOW TABLES
1-- This does NOT work
2SELECT * FROM (SHOW TABLES) AS t;
3-- ERROR 1064: You have an error in your SQL syntax
4
5-- SHOW commands are administrative — they are not standard SQL
6-- and cannot be used as subqueries or table sources
The information_schema.TABLES view gives you the same data as SHOW TABLES but as a regular queryable table:
1-- Equivalent to SHOW TABLES
2SELECT TABLE_NAME
3FROM information_schema.TABLES
4WHERE TABLE_SCHEMA = 'mydb';
5
6-- Filter by pattern
7SELECT TABLE_NAME
8FROM information_schema.TABLES
9WHERE TABLE_SCHEMA = 'mydb'
10 AND TABLE_NAME LIKE 'user%';
11
12-- Get additional metadata
13SELECT TABLE_NAME, ENGINE, TABLE_ROWS, DATA_LENGTH, CREATE_TIME
14FROM information_schema.TABLES
15WHERE TABLE_SCHEMA = 'mydb'
16ORDER BY DATA_LENGTH DESC;
Practical Examples
Find Tables by Column Name
1-- Find all tables that have a column named 'email'
2SELECT DISTINCT TABLE_NAME
3FROM information_schema.COLUMNS
4WHERE TABLE_SCHEMA = 'mydb'
5 AND COLUMN_NAME = 'email';
Find Large Tables
1-- Tables sorted by size (in MB)
2SELECT
3 TABLE_NAME,
4 TABLE_ROWS,
5 ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
6 ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
7 ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS total_mb
8FROM information_schema.TABLES
9WHERE TABLE_SCHEMA = 'mydb'
10ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC
11LIMIT 10;
Count Rows Across All Tables
1-- Approximate row counts for all tables
2SELECT TABLE_NAME, TABLE_ROWS
3FROM information_schema.TABLES
4WHERE TABLE_SCHEMA = 'mydb'
5 AND TABLE_TYPE = 'BASE TABLE'
6ORDER BY TABLE_ROWS DESC;
Note: TABLE_ROWS is an estimate for InnoDB tables. For exact counts, you need SELECT COUNT(*) FROM each_table.
Dynamic Query Generation
Generate SQL statements for all matching tables:
1-- Generate DROP statements for all temp tables
2SELECT CONCAT('DROP TABLE IF EXISTS `', TABLE_NAME, '`;') AS drop_sql
3FROM information_schema.TABLES
4WHERE TABLE_SCHEMA = 'mydb'
5 AND TABLE_NAME LIKE 'tmp_%';
6
7-- Generate TRUNCATE statements
8SELECT CONCAT('TRUNCATE TABLE `', TABLE_NAME, '`;') AS truncate_sql
9FROM information_schema.TABLES
10WHERE TABLE_SCHEMA = 'mydb'
11 AND TABLE_NAME LIKE 'log_%';
Using with Stored Procedures
1DELIMITER //
2
3CREATE PROCEDURE show_tables_with_pattern(IN db_name VARCHAR(64), IN pattern VARCHAR(64))
4BEGIN
5 SELECT TABLE_NAME, ENGINE, TABLE_ROWS
6 FROM information_schema.TABLES
7 WHERE TABLE_SCHEMA = db_name
8 AND TABLE_NAME LIKE pattern
9 ORDER BY TABLE_NAME;
10END //
11
12DELIMITER ;
13
14CALL show_tables_with_pattern('mydb', 'order%');
SHOW TABLES Variants
1-- Show full table type (BASE TABLE vs VIEW)
2SHOW FULL TABLES;
3-- +----------------+------------+
4-- | Tables_in_mydb | Table_type |
5-- +----------------+------------+
6-- | users | BASE TABLE |
7-- | active_users | VIEW |
8-- +----------------+------------+
9
10-- Filter by type using information_schema
11SELECT TABLE_NAME, TABLE_TYPE
12FROM information_schema.TABLES
13WHERE TABLE_SCHEMA = 'mydb'
14 AND TABLE_TYPE = 'VIEW';
Common Pitfalls
Using SHOW TABLES in subqueries: SHOW commands are not SQL statements — they cannot be nested in SELECT, WHERE, or JOIN. Always use information_schema for programmatic access.
TABLE_ROWS is approximate for InnoDB: The TABLE_ROWS column in information_schema.TABLES is an estimate. For exact counts, run SELECT COUNT(*) on each table individually.
Forgetting the TABLE_SCHEMA filter: Without WHERE TABLE_SCHEMA = 'mydb', queries against information_schema.TABLES return tables from ALL databases on the server, which can be slow and confusing.
Case sensitivity with LIKE: On case-sensitive filesystems (Linux), table names in LIKE patterns are case-sensitive. LIKE 'User%' and LIKE 'user%' return different results.
Performance on large schemas: Querying information_schema can be slow on servers with thousands of tables. MySQL 8.0 caches this metadata, but older versions scan table files on disk.
Summary
SHOW TABLES lists tables but cannot be used in subqueries or SELECT FROM
Use information_schema.TABLES for full SQL access to table metadata
Filter by TABLE_SCHEMA (database name) and TABLE_NAME for specific results
information_schema.COLUMNS lets you find tables by column name
Use SHOW FULL TABLES or TABLE_TYPE in information_schema to distinguish base tables from views
