20.3. The INFORMATION_SCHEMA COLUMNS Table

20.3. The `INFORMATION_SCHEMA COLUMNS` Table

The COLUMNS table provides information about columns in tables.

`INFORMATION_SCHEMA` Name	`SHOW` Name	Remarks
`TABLE_CATALOG`		`NULL`
`TABLE_SCHEMA`
`TABLE_NAME`
`COLUMN_NAME`	`Field`
`ORDINAL_POSITION`		see notes
`COLUMN_DEFAULT`	`Default`
`IS_NULLABLE`	`Null`
`DATA_TYPE`	`Type`
`CHARACTER_MAXIMUM_LENGTH`	`Type`
`CHARACTER_OCTET_LENGTH`
`NUMERIC_PRECISION`	`Type`
`NUMERIC_SCALE`	`Type`
`CHARACTER_SET_NAME`
`COLLATION_NAME`	`Collation`
`COLUMN_TYPE`	`Type`	MySQL extension
`COLUMN_KEY`	`Key`	MySQL extension
`EXTRA`	`Extra`	MySQL extension
`COLUMN_COMMENT`	`Comment`	MySQL extension

Notes:

In SHOW, the Type display includes values from several different COLUMNS columns.
ORDINAL_POSITION is necessary because you might want to say ORDER BY ORDINAL_POSITION. Unlike SHOW, SELECT does not have automatic ordering.
CHARACTER_OCTET_LENGTH should be the same as CHARACTER_MAXIMUM_LENGTH, except for multi-byte character sets.
CHARACTER_SET_NAME can be derived from Collation. For example, if you say SHOW FULL COLUMNS FROM t, and you see in the Collation column a value of latin1_swedish_ci, the character set is what's before the first underscore: latin1.

The following statements are nearly equivalent:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'tbl_name'
  [AND table_schema = 'db_name']
  [AND column_name LIKE 'wild']

SHOW COLUMNS
  FROM tbl_name
  [FROM db_name]
  [LIKE 'wild']