Practice Now
MySQL
SQL Query
Database Management
Show Tables
Data Selection

Select data from show tables MySQL query

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Databases

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

sql
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

sql
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

Using information_schema Instead

The information_schema.TABLES view gives you the same data as SHOW TABLES but as a regular queryable table:

sql
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

sql
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

sql
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

sql
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:

sql
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

sql
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

sql
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
Related reading
Course
Beginner
27 lessons
10 hours
System Design Fundamentals

Build a strong foundation in designing scalable, reliable distributed systems.

View the course
Track what you have practised

A free account saves your progress, solutions and study plan across every problem on Codemia.

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Codemia
© 2026 Codemia
Resources
Legal
Social
All Rights Reserved.