MySQL SHOW COLUMNS and DESCRIBE: List All Columns in a Table
Both the SHOW COLUMNS and DESCRIBE commands in MySQL display the structure of a table, including details about its columns such as names, data types, and constraints.
1. Using SHOW COLUMNS
The SHOW COLUMNS command provides detailed information about all columns in a table.
Syntax
Example
✅ Output
This will display a list of columns in the employees table, along with their data types, whether they can be NULL, any default values, and if they are part of a PRIMARY KEY, etc.
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | NULL | auto_increment |
| first_name | varchar(100) | YES | NULL | ||
| last_name | varchar(100) | YES | NULL | ||
| hire_date | date | YES | NULL |
2. Using DESCRIBE
The DESCRIBE command is essentially a shortcut for SHOW COLUMNS. It gives the same output, making it easy to quickly inspect the table's structure.
Syntax
Example
✅ Output
Similar to SHOW COLUMNS, it will list all columns in the employees table with their details.
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | NULL | auto_increment |
| first_name | varchar(100) | YES | NULL | ||
| last_name | varchar(100) | YES | NULL | ||
| hire_date | date | YES | NULL |
3. Key Differences Between SHOW COLUMNS and DESCRIBE
- Functionality: Both
SHOW COLUMNSandDESCRIBEdo the same thing; they list column details for a given table. - Syntax:
DESCRIBEis a shorthand forSHOW COLUMNS. It is a bit more convenient to use in simple queries.
4. Additional Information with SHOW TABLE STATUS
If you need more information about a table, such as its storage engine, number of rows, or when it was last updated, you can use the SHOW TABLE STATUS command.
Example
✅ Output
This will show additional metadata about the employees table.
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time |
|---|---|---|---|---|---|---|---|---|---|---|---|
| employees | InnoDB | 10 | Compact | 1000 | 150 | 150000 | 50000 | 0 | 2001 | 2023-01-01 12:00:00 | 2023-01-02 13:00:00 |
5. Conclusion
- SHOW COLUMNS and DESCRIBE list all columns in a table along with their properties.
- DESCRIBE is simply a shortcut for SHOW COLUMNS.
- For more detailed table metadata, use
SHOW TABLE STATUS.
š Quickly inspect and analyze the structure of your tables with these commands!
