SHOW TABLES and DESCRIBE in MySQL: Inspecting Database Structure

Once a database has several tables, each with their own columns, types, and constraints, you need fast ways to inspect what's actually there without opening the original CREATE TABLE script. MySQL provides a set of simple, instant commands for exactly this: SHOW TABLES, DESCRIBE, SHOW COLUMNS, and SHOW CREATE TABLE. This lesson covers each one and when to reach for it.

What Are SHOW and DESCRIBE Commands?

SHOW and DESCRIBE are MySQL's built-in metadata inspection commands. SHOW TABLES lists every table in the currently selected database, while DESCRIBE (or its full SHOW COLUMNS FROM equivalent) reveals a specific table's column names, data types, keys, and default values. They query MySQL's internal metadata rather than the actual row data inside the tables.

What You'll Learn

• List all tables in the current database using SHOW TABLES.
• Inspect a table's column structure using DESCRIBE.
• Use SHOW CREATE TABLE to retrieve the exact statement that built a table.
• Know when to reach for each metadata inspection command.

Key Terms to Know

• SHOW TABLES: A command listing all tables in the currently selected database.
• DESCRIBE (DESC): A command showing a table's columns, types, keys, and defaults.
• SHOW COLUMNS FROM: The full equivalent of DESCRIBE, listing detailed column metadata.
• SHOW CREATE TABLE: A command returning the exact CREATE TABLE statement used to build a table as it currently exists.

SHOW TABLES and SHOW DATABASES

SHOW DATABASES; lists every database visible to the current user on the server. After switching into one with USE database_name;, SHOW TABLES; lists every table that exists inside it. This is usually the very first command run when exploring an unfamiliar database, before drilling into any specific table.

DESCRIBE and SHOW COLUMNS: Inspecting a Table's Columns

DESCRIBE table_name; (often shortened to DESC table_name;) returns each column's name, data type, whether NULL is allowed, key information (PRI for primary key, UNI for unique, MUL for indexed/foreign key), default value, and any extra attributes like auto_increment.

SHOW COLUMNS FROM table_name; returns the exact same information as DESCRIBE — DESCRIBE is simply a shorthand alias for it in MySQL. Both are read-only inspection commands; they never modify the table.

SHOW CREATE TABLE: Getting the Exact Schema Definition

SHOW CREATE TABLE table_name; returns the complete CREATE TABLE statement that would recreate the table exactly as it currently stands, including all constraints, indexes, foreign keys, character set, and storage engine — information DESCRIBE alone doesn't fully show.

This is especially useful when documenting an existing schema, debugging why a constraint isn't behaving as expected, or copying a table's exact structure into a new environment.

Visual Summary

Picture walking into an unfamiliar library. SHOW DATABASES is the directory of buildings on the campus. SHOW TABLES, once inside one building, is the floor directory listing every room (table) on that floor. DESCRIBE is opening one room's inventory sheet, listing every shelf (column) and what it's allowed to hold. SHOW CREATE TABLE is the full blueprint that would let you construct an identical room from scratch.

Metadata Inspection Commands

Command	Shows	Typical Use
SHOW DATABASES;	All databases on the server	First step exploring a server
SHOW TABLES;	All tables in the current database	First step exploring a database
DESCRIBE table_name;	Columns, types, keys, defaults	Quick column structure check
SHOW COLUMNS FROM table_name;	Same as DESCRIBE	Equivalent, more verbose form
SHOW CREATE TABLE table_name;	Exact CREATE TABLE statement	Full schema, constraints, indexes

SQL Example

-- Explore what's available
SHOW DATABASES;
USE school_management;
SHOW TABLES;

-- Inspect a specific table's columns
DESCRIBE students;

-- Equivalent verbose form
SHOW COLUMNS FROM students;

-- Get the exact statement that built the table, including constraints
SHOW CREATE TABLE students;

This sequence mirrors how a developer typically explores an unfamiliar database: list databases, switch into the relevant one, list its tables, then drill into a specific table's columns with DESCRIBE, and finally pull the full schema definition with SHOW CREATE TABLE when constraint or index details are needed.

Real-World Examples

• New engineers joining a company run SHOW TABLES and DESCRIBE on unfamiliar databases to quickly understand the existing schema before writing any code.
• Database administrators use SHOW CREATE TABLE to document schemas or to copy an exact table structure into a staging environment.
• Debugging tools and admin panels often call DESCRIBE programmatically to dynamically render forms matching a table's actual columns.
• Support engineers use DESCRIBE during incident investigation to quickly confirm a column's data type or default value without digging through old migration files.

Best Practices and Pro Tips

• Use DESCRIBE as your first move whenever you're unsure of a column's exact name, type, or default — it's faster than searching through migration history.
• Reach for SHOW CREATE TABLE specifically when you need full constraint, index, or foreign key details that DESCRIBE alone doesn't surface.
• In GUI tools like MySQL Workbench or phpMyAdmin, the same information is available visually, but knowing the raw commands is essential for quick CLI access or scripting.

Common Mistakes to Avoid

• Confusing DESCRIBE (a MySQL metadata command) with the EXPLAIN command, which analyzes query execution plans — they look similar but serve completely different purposes.
• Forgetting to run USE database_name; first, leading to a 'no database selected' error when running SHOW TABLES.
• Assuming DESCRIBE shows foreign key and index details — for that level of detail, SHOW CREATE TABLE is needed instead.
• Relying on DESCRIBE output without realizing it reflects the live, current table structure, not the original CREATE TABLE script, which may have changed since via ALTER TABLE.

Interview Questions

Q1. What is the difference between DESCRIBE and SHOW CREATE TABLE?

DESCRIBE gives a concise summary of each column's name, type, nullability, key, and default. SHOW CREATE TABLE returns the complete CREATE TABLE statement, including full constraint definitions, indexes, foreign keys, and storage engine details that DESCRIBE doesn't show.

Q2. Are DESCRIBE and SHOW COLUMNS FROM different commands?

No, DESCRIBE is simply a shorthand alias for SHOW COLUMNS FROM in MySQL — both return identical column metadata for a table.

Q3. What command would you use to see all tables in the currently selected database?

SHOW TABLES; lists every table that exists in the database currently selected via USE database_name;.

Q4. Is DESCRIBE the same as the EXPLAIN command?

No. DESCRIBE shows a table's column structure. EXPLAIN analyzes how MySQL plans to execute a given query, showing details like index usage — they are unrelated despite occasionally similar-sounding names.

Practice MCQs

1. Which command lists every table in the currently selected database?

1. SHOW DATABASES;
2. SHOW TABLES;
3. DESCRIBE;
4. SHOW CREATE TABLE;

Answer: B. SHOW TABLES;

Explanation: SHOW TABLES; lists all tables that exist within the database currently selected with USE.

2. Which command returns the exact CREATE TABLE statement for an existing table, including constraints?

1. DESCRIBE table_name;
2. SHOW COLUMNS FROM table_name;
3. SHOW CREATE TABLE table_name;
4. SHOW TABLES;

Answer: C. SHOW CREATE TABLE table_name;

Explanation: SHOW CREATE TABLE returns the full statement needed to recreate the table exactly, including constraints and indexes.

3. DESCRIBE table_name; is shorthand for which other command?

1. SHOW TABLES;
2. SHOW DATABASES;
3. SHOW COLUMNS FROM table_name;
4. SHOW CREATE TABLE table_name;

Answer: C. SHOW COLUMNS FROM table_name;

Explanation: DESCRIBE is a MySQL alias that returns the same column metadata as SHOW COLUMNS FROM.

Quick Revision Points

• SHOW DATABASES lists servers' databases; SHOW TABLES lists tables in the current database.
• DESCRIBE = SHOW COLUMNS FROM, summarizing column name, type, key, default, extra.
• SHOW CREATE TABLE returns the full exact statement, including constraints and indexes.
• DESCRIBE is unrelated to EXPLAIN, which analyzes query execution plans.

Conclusion

• SHOW and DESCRIBE commands are the fastest way to understand an unfamiliar database without reading old migration scripts.
• DESCRIBE covers quick column checks; SHOW CREATE TABLE covers full structural detail.
• These commands are read-only and completely safe to run on any database, including production.

SHOW DATABASES, SHOW TABLES, DESCRIBE, and SHOW CREATE TABLE together form MySQL's everyday toolkit for inspecting database and table structure quickly, without needing access to original schema scripts. DESCRIBE gives a fast column-level summary, while SHOW CREATE TABLE provides the complete, exact definition including constraints and indexes. With inspection commands covered, the final structural topic in this module is constraints — revisiting all six SQL constraint types together in one place.