MySQL 5.1 Reference Manual  /  INFORMATION_SCHEMA Tables  /  The INFORMATION_SCHEMA COLUMNS Table

20.4 The INFORMATION_SCHEMA COLUMNS Table

The COLUMNS table provides information about columns in tables.

INFORMATION_SCHEMA NameSHOW NameRemarks
TABLE_CATALOG NULL
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAMEField
ORDINAL_POSITION see notes
COLUMN_DEFAULTDefault
IS_NULLABLENull
DATA_TYPEType
CHARACTER_MAXIMUM_LENGTHType
CHARACTER_OCTET_LENGTH
NUMERIC_PRECISIONType
NUMERIC_SCALEType
CHARACTER_SET_NAME
COLLATION_NAMECollation
COLUMN_TYPETypeMySQL extension
COLUMN_KEYKeyMySQL extension
EXTRAExtraMySQL extension
PRIVILEGESPrivilegesMySQL extension
COLUMN_COMMENTCommentMySQL 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 multibyte 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 is 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']

User Comments
  Posted by Gelo Tiglao on April 12, 2007
This is the equivalent syntax for "all_tab_columns" that is used for Oracle

-Gelomon
  Posted by Jens Lærkedal Pedersen on August 22, 2010
When using select on INFORMATION_SCHEMA.COLUMNS, observe that it is independent of previous USE <db> statements. This means you should take care if you omit the "TABLE_SCHEMA = <db>". Your result could be a mix of data from other DB instances. This may be trivial, but it did fool me.
  Posted by Shailesh Humbad on September 7, 2015
It should be noted that INFORMATION_SCHEMA.COLUMNS does NOT contain information about temporary tables. To get temporary table column information, you must use SHOW COLUMNS. You can also use SHOW CREATE TABLE or in MySQL 5.7.1, INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO.
Sign Up Login You must be logged in to post a comment.