Inspecting Tables with DESCRIBE & SHOW CREATE0%

Inspecting Tables with DESCRIBE & SHOW CREATE

Beginner12 min read•Updated: 2026-09-12

Inspecting Tables: DESCRIBE, EXPLAIN, and SHOW CREATE TABLE

When stepping into a new company or taking over an existing codebase, you rarely start with a clean slate. You must inspect existing database schemas, verify column data types, check nullability, examine default values, and review exact table definitions. MySQL provides three essential introspection commands: SHOW TABLES, DESCRIBE, and SHOW CREATE TABLE.

1. Listing Tables in the Active Database (`SHOW TABLES`)

To view all tables, views, and sequence objects in your active database:

1SHOW TABLES;
2 
3-- Filter tables matching a specific prefix pattern:
4SHOW TABLES LIKE 'user_%';

2. Inspecting Column Definitions with `DESCRIBE`

The DESCRIBE statement (shorthand: DESC) gives a structured overview of a table's columns:

1DESCRIBE customers;
2-- Or using shorthand:
3DESC customers;

Sample `DESCRIBE` Output:

Visual Architecture Blueprint

+----------------+------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field          | Type                                           | Null | Key | Default           | Extra                                         |
+----------------+------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| customer_id    | int unsigned                                   | NO   | PRI | NULL              | auto_increment                                |
| first_name     | varchar(50)                                    | NO   |     | NULL              |                                               |
| email          | varchar(255)                                   | NO   | UNI | NULL              |                                               |
| account_status | enum('Active','Suspended','Pending_Verificati')| NO   |     | Pending_Verificati|                                               |
| created_at     | timestamp                                      | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED                             |
| updated_at     | timestamp                                      | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP  |
+----------------+------------------------------------------------+------+-----+-------------------+-----------------------------------------------+

Understanding Key Indicators:

Field: Column name.
Type: Exact data type and length.
Null: Indicates whether the column accepts NULL values (YES or NO).
Key:
PRI: Primary Key.
UNI: Unique Key constraint.
MUL: Multiple (non-unique index, or the first column of a composite index).
Default: Default fallback value if none is provided.
Extra: Additional flags like auto_increment or on update CURRENT_TIMESTAMP.

3. Retrieving Exact DDL with `SHOW CREATE TABLE`

While DESCRIBE shows a tabular summary, it does not reveal foreign key constraint definitions, storage engine settings, character sets, or index names. To see the exact SQL code required to recreate the table from scratch:

1SHOW CREATE TABLE customersG

Sample Output:

1*************************** 1. row ***************************
2       Table: customers
3Create Table: CREATE TABLE `customers` (
4  `customer_id` int unsigned NOT NULL AUTO_INCREMENT,
5  `first_name` varchar(50) COLLATE utf8mb4_0900_ai_ci NOT NULL,
6  `email` varchar(255) COLLATE utf8mb4_0900_ai_ci NOT NULL,
7  PRIMARY KEY (`customer_id`),
8  UNIQUE KEY `uq_customer_email` (`email`)
9) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

4. Querying Column Metadata via `information_schema.COLUMNS`

For automated tooling, ORMs, and reporting scripts, you can query column metadata using standard SQL:

1SELECT 
2    column_name, 
3    data_type, 
4    is_nullable, 
5    column_default,
6    character_maximum_length
7FROM information_schema.columns
8WHERE table_schema = 'ecommerce_platform' 
9  AND table_name = 'customers'
10ORDER BY ordinal_position;

5. Best Practices & Common Pitfalls

Use \G with SHOW CREATE TABLE: In the MySQL CLI, always end SHOW CREATE TABLE with \G instead of a semicolon. This prevents ugly text wrapping and prints the DDL cleanly!
Verify Key Multiplicity (MUL): A MUL key in DESC output signifies that the column is indexed, but allows duplicate values. It is commonly found on foreign key columns.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which shorthand command is identical to DESCRIBE table_name; in MySQL?

Question 2

In the output of DESCRIBE customers;, what does the value PRI in the Key column signify?

Question 3

Which SQL statement reveals the exact DDL statement used to create a table, including all indexes, storage engines, and collations?

Question 4

What does the MUL indicator in the Key column of DESCRIBE output mean?

Question 5

Which administrative view in information_schema contains exhaustive metadata for every column across all database tables?

Next Lesson

Modifying Tables with ALTER TABLE

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Creating Tables Syntax & Rules	Modifying Tables with ALTER TABLE

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Modifying Tables with ALTER TABLE

Inspecting Tables with DESCRIBE & SHOW CREATE

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Inspecting Tables: DESCRIBE, EXPLAIN, and SHOW CREATE TABLE

1. Listing Tables in the Active Database (`SHOW TABLES`)

To view all tables, views, and sequence objects in your active database:

1SHOW TABLES;
2 
3-- Filter tables matching a specific prefix pattern:
4SHOW TABLES LIKE 'user_%';

2. Inspecting Column Definitions with `DESCRIBE`

The DESCRIBE statement (shorthand: DESC) gives a structured overview of a table's columns:

1DESCRIBE customers;
2-- Or using shorthand:
3DESC customers;

Sample `DESCRIBE` Output:

Visual Architecture Blueprint

+----------------+------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field          | Type                                           | Null | Key | Default           | Extra                                         |
+----------------+------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| customer_id    | int unsigned                                   | NO   | PRI | NULL              | auto_increment                                |
| first_name     | varchar(50)                                    | NO   |     | NULL              |                                               |
| email          | varchar(255)                                   | NO   | UNI | NULL              |                                               |
| account_status | enum('Active','Suspended','Pending_Verificati')| NO   |     | Pending_Verificati|                                               |
| created_at     | timestamp                                      | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED                             |
| updated_at     | timestamp                                      | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP  |
+----------------+------------------------------------------------+------+-----+-------------------+-----------------------------------------------+

Understanding Key Indicators:

Field: Column name.
Type: Exact data type and length.
Null: Indicates whether the column accepts NULL values (YES or NO).
Key:
PRI: Primary Key.
UNI: Unique Key constraint.
MUL: Multiple (non-unique index, or the first column of a composite index).
Default: Default fallback value if none is provided.
Extra: Additional flags like auto_increment or on update CURRENT_TIMESTAMP.

3. Retrieving Exact DDL with `SHOW CREATE TABLE`

1SHOW CREATE TABLE customersG

Sample Output:

1*************************** 1. row ***************************
2       Table: customers
3Create Table: CREATE TABLE `customers` (
4  `customer_id` int unsigned NOT NULL AUTO_INCREMENT,
5  `first_name` varchar(50) COLLATE utf8mb4_0900_ai_ci NOT NULL,
6  `email` varchar(255) COLLATE utf8mb4_0900_ai_ci NOT NULL,
7  PRIMARY KEY (`customer_id`),
8  UNIQUE KEY `uq_customer_email` (`email`)
9) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

4. Querying Column Metadata via `information_schema.COLUMNS`

For automated tooling, ORMs, and reporting scripts, you can query column metadata using standard SQL:

1SELECT 
2    column_name, 
3    data_type, 
4    is_nullable, 
5    column_default,
6    character_maximum_length
7FROM information_schema.columns
8WHERE table_schema = 'ecommerce_platform' 
9  AND table_name = 'customers'
10ORDER BY ordinal_position;

5. Best Practices & Common Pitfalls

Use \G with SHOW CREATE TABLE: In the MySQL CLI, always end SHOW CREATE TABLE with \G instead of a semicolon. This prevents ugly text wrapping and prints the DDL cleanly!
Verify Key Multiplicity (MUL): A MUL key in DESC output signifies that the column is indexed, but allows duplicate values. It is commonly found on foreign key columns.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which shorthand command is identical to DESCRIBE table_name; in MySQL?

Question 2

In the output of DESCRIBE customers;, what does the value PRI in the Key column signify?

Question 3

Which SQL statement reveals the exact DDL statement used to create a table, including all indexes, storage engines, and collations?

Question 4

What does the MUL indicator in the Key column of DESCRIBE output mean?

Question 5

Which administrative view in information_schema contains exhaustive metadata for every column across all database tables?

Next Lesson

Modifying Tables with ALTER TABLE

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Creating Tables Syntax & Rules	Modifying Tables with ALTER TABLE

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Inspecting Tables: DESCRIBE, EXPLAIN, and SHOW CREATE TABLE

1. Listing Tables in the Active Database (SHOW TABLES)

2. Inspecting Column Definitions with DESCRIBE

Sample DESCRIBE Output:

Understanding Key Indicators:

3. Retrieving Exact DDL with SHOW CREATE TABLE

Sample Output:

4. Querying Column Metadata via information_schema.COLUMNS

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Modifying Tables with ALTER TABLE

Related Lessons

Practice Quiz

Loading Page Content...

Inspecting Tables: DESCRIBE, EXPLAIN, and SHOW CREATE TABLE

1. Listing Tables in the Active Database (SHOW TABLES)

2. Inspecting Column Definitions with DESCRIBE

Sample DESCRIBE Output:

Understanding Key Indicators:

3. Retrieving Exact DDL with SHOW CREATE TABLE

Sample Output:

4. Querying Column Metadata via information_schema.COLUMNS

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Modifying Tables with ALTER TABLE

Related Lessons

Practice Quiz

1. Listing Tables in the Active Database (`SHOW TABLES`)

2. Inspecting Column Definitions with `DESCRIBE`

Sample `DESCRIBE` Output:

3. Retrieving Exact DDL with `SHOW CREATE TABLE`

4. Querying Column Metadata via `information_schema.COLUMNS`

1. Listing Tables in the Active Database (`SHOW TABLES`)

2. Inspecting Column Definitions with `DESCRIBE`

Sample `DESCRIBE` Output:

3. Retrieving Exact DDL with `SHOW CREATE TABLE`

4. Querying Column Metadata via `information_schema.COLUMNS`