(mysql.info.gz) COLUMNS Table
Info Catalog
(mysql.info.gz) TABLES Table
(mysql.info.gz) INFORMATION_SCHEMA Tables
(mysql.info.gz) STATISTICS Table
21.1.3 The `INFORMATION_SCHEMA COLUMNS' Table
---------------------------------------------
The `COLUMNS' table provides information about columns in tables.
*Standard 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_PRECISION_RADIX' omit
`NUMERIC_SCALE' `Type'
`DATETIME_PRECISION' omit
`INTERVAL_TYPE' omit
`INTERVAL_PRECISION' omit
`CHARACTER_SET_CATALOG' omit
`CHARACTER_SET_SCHEMA' omit
`CHARACTER_SET_NAME'
`COLLATION_CATALOG' omit
`COLLATION_SCHEMA' omit
`COLLATION_NAME' `Collation'
`DOMAIN_NAME' omit
`UDT_CATALOG' omit
`UDT_SCHEMA' omit
`UDT_NAME' omit
`SCOPE_CATALOG' omit
`SCOPE_SCHEMA' omit
`SCOPE_NAME' omit
`MAXIMUM_CARDINALITY' omit
`DTD_IDENTIFIER' omit
`IS_SELF_REFERENCING' omit
`IS_IDENTITY' omit
`IDENTITY_GENERATION' omit
`IDENTITY_START' omit
`IDENTITY_INCREMENT' omit
`IDENTITY_MAXIMUM' omit
`IDENTITY_MINIMUM' omit
`IDENTITY_CYCLE' omit
`IS_GENERATED' omit
`GENERATION_EXPRESSION' omit
`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 someday 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 schema_name = 'db_name']
[AND column_name LIKE 'wild']
SHOW COLUMNS
FROM tbl_name
[FROM db_name]
[LIKE wild]
Info Catalog
(mysql.info.gz) TABLES Table
(mysql.info.gz) INFORMATION_SCHEMA Tables
(mysql.info.gz) STATISTICS Table
automatically generated byinfo2html