Show Columns in MySQL
Introduction
When working with tables, you often need to view the details of columns, such as their names, data types, and constraints.
MySQL provides the SHOW COLUMNS command to display this information clearly.
What is SHOW COLUMNS
The SHOW COLUMNS statement is used to display the column details of a table.
It provides information such as:
Column names
Data types
NULL values
Keys (PRIMARY, UNIQUE)
Default values
Basic Syntax
SHOW COLUMNS FROM table_name; Example
SHOW COLUMNS FROM Students; Output may look like:
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| id | INT | NO | PRI | NULL | auto_increment |
| name | VARCHAR(50) | YES | NULL | ||
| VARCHAR(100) | YES | NULL |
Using SHOW COLUMNS with LIKE
You can filter columns using LIKE:
SHOW COLUMNS FROM Students LIKE 'n%'; This shows columns starting with n.
Difference Between SHOW COLUMNS and DESCRIBE
| Feature | SHOW COLUMNS | DESCRIBE |
|---|---|---|
| Purpose | Show column details | Show table structure |
| Syntax | SHOW COLUMNS FROM | DESC table_name |
| Output | Similar | Similar |
Both commands give similar results.
Example Scenario
Before inserting data, you can check columns:
SHOW COLUMNS FROM Students; This helps you understand what data needs to be inserted.
Common Mistakes
Using the wrong table name
Not selecting the database
Confusing with SHOW TABLES
Key Points to Remember
SHOW COLUMNSdisplays column detailsHelps understand table structure
Similar to the DESCRIBE command
Can filter using LIKE