Skip navigation links
MySQL 5.0 Reference Manual :: 19 INFORMATION_SCHEMA Tables :: 19.4 The INFORMATION_SCHEMA COLUMNS Table
19.5 The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table »
Section Navigation      [Toggle]

19.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 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 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']
Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices

User Comments

Posted by Gelo Tiglao on April 12 2007 12:25am[Delete] [Edit]

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 11:41am[Delete] [Edit]

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.

Add your own comment.