Name

SHOW COLUMNS

Synopsis

SHOW [FULL] COLUMNS FROM table [FROM database] [LIKE 'pattern']

Use this statement to display the columns for a given table. If the table is not in the current default database, the FROMdatabase clause may be given to name another database. You can use the LIKE clause to list only columns that match a naming pattern given in quotes.

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 are retrieved. The following example is for just 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 information on collation and the user’s privileges with regard to the column is provided.

Get MySQL in a Nutshell now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.