Table of Contents for

MySQL in a Nutshell, 2nd Edition

Use this statement to display the columns for a given table. If the table is not in the current default database, the FROM database clause may be given to specify another database. You can use the LIKE clause to list only columns that match a naming pattern given in quotes. Or you may use the WHERE clause to refine the results set. The FULL flag will return the name of the character set used for collating and the user privileges of the current session for the columns returned:

SHOW COLUMNS FROM clients FROM workrequests LIKE 'client%';

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| client_id   | varchar(4)  |      | PRI |         |       |
| client_name | varchar(50) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

In this example, only information for columns beginning with the name client is retrieved. The following example is just for the client_id column and uses the FULL flag along with the alternate display method (\G):

SHOW FULL COLUMNS FROM clients FROM workrequests
LIKE 'client_id'\G
*************************** 1. row ***************************
     Field: client_id
      Type: varchar(4)
 Collation: latin1_swedish_ci
      Null:
       Key: PRI
   Default:
     Extra:
Privileges: select,insert,update,references
   Comment:

Notice that the name of the collation used for the column (latin1_swedish_ci) and the user’s privileges (SELECT, INSERT, UPDATE, and REFERENCES) with regard to the column are provided.