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:

Field is not used as part of a key.
The column is the first column in a non-unique index.
The column is the primary key or part of the primary key.
The column is the first column of a unique value index.

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'


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.

About the author: is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.