Syntax:
SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

SHOW COLUMNS displays information about the columns in a given table.
It also works for views as of MySQL 5.0.1. The LIKE clause, if present,
indicates which column names to match. The WHERE clause can be given to
select rows using more general conditions, as discussed in
http://dev.mysql.com/doc/refman/5.0/en/extended-show.html.

mysql> SHOW COLUMNS FROM City;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

If the data types differ from what you expect them to be based on a
CREATE TABLE statement, note that MySQL sometimes changes data types
when you create or alter a table. The conditions under which this
occurs are described in
http://dev.mysql.com/doc/refman/5.0/en/silent-column-changes.html.

The FULL keyword causes the output to include the column collation and
comments, as well as the privileges you have for each column.

You can use db_name.tbl_name as an alternative to the tbl_name FROM
db_name syntax. In other words, these two statements are equivalent:

mysql> SHOW COLUMNS FROM mytable FROM mydb;
mysql> SHOW COLUMNS FROM mydb.mytable;

SHOW COLUMNS displays the following values for each table column:

Field indicates the column name.

Type indicates the column data type.

Collation indicates the collation for non-binary string columns, or
NULL for other columns. This value is displayed only if you use the
FULL keyword.

The Null field contains YES if NULL values can be stored in the column.
If not, the column contains NO as of MySQL 5.0.3, and '' before that.

The Key field indicates whether the column is indexed:

o If Key is empty, the column either is not indexed or is indexed only
  as a secondary column in a multiple-column, non-unique index.

o If Key is PRI, the column is a PRIMARY KEY or is one of the columns
  in a multiple-column PRIMARY KEY.

o If Key is UNI, the column is the first column of a unique-valued
  index that cannot contain NULL values.

o If Key is MUL, multiple occurrences of a given value are allowed
  within the column. The column is the first column of a non-unique
  index or a unique-valued index that can contain NULL values.

If more than one of the Key values applies to a given column of a
table, Key displays the one with the highest priority, in the order
PRI, UNI, MUL.

A UNIQUE index may be displayed as PRI if it cannot contain NULL values
and there is no PRIMARY KEY in the table. A UNIQUE index may display as
MUL if several columns form a composite UNIQUE index; although the
combination of the columns is unique, each column can still hold
multiple occurrences of a given value.

Before MySQL 5.0.11, if the column allows NULL values, the Key value
can be MUL even when a single-column UNIQUE index is used. The
rationale was that multiple rows in a UNIQUE index can hold a NULL
value if the column is not declared NOT NULL. As of MySQL 5.0.11, the
display is UNI rather than MUL regardless of whether the column allows
NULL; you can see from the Null field whether or not the column can
contain NULL.

The Default field indicates the default value that is assigned to the
column.

The Extra field contains any additional information that is available
about a given column. The value is auto_increment if the column was
created with the AUTO_INCREMENT keyword and empty otherwise.

Privileges indicates the privileges you have for the column. This value
is displayed only if you use the FULL keyword.

Comment indicates any comment the column has. This value is displayed
only if you use the FULL keyword.

SHOW FIELDS is a synonym for SHOW COLUMNS. You can also list a table's
columns with the mysqlshow db_name tbl_name command.

The DESCRIBE statement provides information similar to SHOW COLUMNS.
See [HELP DESCRIBE].

The SHOW CREATE TABLE, SHOW TABLE STATUS, and SHOW INDEX statements
also provide information about tables. See [HELP SHOW].

URL: http://dev.mysql.com/doc/refman/5.0/en/show-columns.html

