SHOW COLUMNS

With this MySQL statement you can display the columns for a table: their datatype, whether they're indexed, default value, etc..

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

SHOW [FULL] COLUMNS FROM table [FROM database] [LIKE 'pattern'|WHERE expression]

Explanation

Use this MySQL statement to display the columns for a given MySQL table. If the MySQL 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 name of the character set used for collating and the user privileges of the current MySQL session for the columns returned.

Examples

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 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 (i.e., latin1_swedish_ci), and the user's privileges in MySQL (i.e., SELECT, INSERT, UPDATE, and REFERENCES) with regard to the MySQL column are provided.