Cry MySQL How to...

List all the fields in a table

---

To list all of the fields in a given table use:

desc mytable

where 'mytable' is the name of the table for which you want to see the fields.

What this will return is a table listing all the fields and information about those fields with one row fore each field.

For example:

desc accounts

might generate:

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| UserID    | int(11)      | NO   | PRI | NULL    | auto_increment |
| username  | varchar(64)  | NO   | UNI | NULL    |                |
| hash      | varchar(128) | NO   |     | NULL    |                |
| LastLogin | datetime     | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

Where Null indicates whether the field can contain a null value, and Key will be one of:

If you are only interested in a specific field then you can instead use:

desc mytable somefield

"desc" is short for "describe", and both of these are in turn a short form of "show columns from table", so each of the following are equivalent and will produce the same identical output:

desc mytable;
describe mytable;
show columns from mytable;

whilst it is longer the "show columns from mytable" is slightly more flexible and it can give you slightly more information.

show full columns from mytable

the word "full" will show the collation used for the column, as well as any privileges you have on the column and any comment associated with the column. It also supports more options for selecting which columns are reported on, supporting either:

show full columns from mytable like 'pattern'

or

show full columns from mytable where expression

---

These notes have been tested against MySQL version 5.1, and may apply to other versions as well.