DESCRIBE - MySQL

Overview

DESCRIBE provides metadata about a table or query result, including its columns, data types, and any indexes or constraints. It is particularly useful for examining table structure, understanding field properties, and troubleshooting queries.

Syntax

DESCRIBE [table_name]

Options/Flags

None.

Examples

Get metadata for a table:

DESCRIBE users;

Output:

Field | Type | Null | Key | Default | Extra
-------|------|------|------|---------|--------
id | int(11) | NO | PRI | NULL | auto_increment
name | varchar(255) | YES | | NULL |
email | varchar(255) | YES | UNI | NULL |
created_at | timestamp | YES | | CURRENT_TIMESTAMP |

Get metadata for a query result:

SELECT * FROM users WHERE id = 1;
DESCRIBE;

Output:

Field | Type | Null | Key | Default | Extra
-------|------|------|------|---------|--------
id | int(11) | NO | PRI | NULL | auto_increment
name | varchar(255) | YES | | NULL |
email | varchar(255) | YES | UNI | NULL |
created_at | timestamp | YES | | CURRENT_TIMESTAMP |

Common Issues

Error: Table doesn’t exist

Make sure the specified table exists in the current database.

Error: Incorrect syntax near ‘DESCRIBE’

Check the spelling of DESCRIBE. It should be followed by the table name without a semicolon.

Integration

Combining with other commands:

SELECT * FROM users WHERE id = (SELECT id FROM users WHERE name = 'John');
DESCRIBE;

Using in scripts:

# Print metadata for all tables in the database
for table in $(mysql -e "SHOW TABLES;" | grep -v Tables_in_); do
    echo -e "\nDESCRIBE `$table`;" | mysql
done
  • SHOW COLUMNS: Provides more detailed information about table columns, including collations and character sets.
  • SHOW INDEX: Displays information about table indexes.
  • EXPLAIN: Analyzes and visualizes query execution plans, which can benefit from table metadata provided by DESCRIBE.