View a single column definition in MySQL

Most MySQL developers will be familiar with the DESCRIBE statements, which shows the definition of all the columns in a table:

> DESCRIBE contacts;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id    | bigint(20) unsigned | NO   | MUL | NULL    |                |
| client_id  | bigint(20) unsigned | YES  | MUL | NULL    |                |
| name       | varchar(255)        | NO   |     | NULL    |                |
| created_at | timestamp           | YES  |     | NULL    |                |
| updated_at | timestamp           | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
6 rows in set (0.002 sec)

Sometimes you want to look at a single column, which can be tricky in larger tables (I have worked with some that have 50+ columns). Fortunately there is another statement, SHOW COLUMNS, which will output the same as DESCRIBE:

> SHOW COLUMNS FROM contacts;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id    | bigint(20) unsigned | NO   | MUL | NULL    |                |
| client_id  | bigint(20) unsigned | YES  | MUL | NULL    |                |
| name       | varchar(255)        | NO   |     | NULL    |                |
| created_at | timestamp           | YES  |     | NULL    |                |
| updated_at | timestamp           | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
6 rows in set (0.002 sec)

The advantage of SHOW COLUMNS is that it supports WHERE clauses in the same way as SELECT etc. For example, you can view a single column:

> SHOW COLUMNS FROM contacts WHERE Field = 'name';
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(255) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.002 sec)

Unfortunately you can only filter on the Field column, so it isn’t possible to use WHERE Null = ‘YES’ to obtain all columns that allow NULL values.

SHOW COLUMNS works in MySQL and MariaDB.

Comments

No comments yet. Why don’t you start the discussion?

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.