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.