Skip navigation links
MySQL 5.1 with Maria Reference Manual :: 19 INFORMATION_SCHEMA Tables :: 19.3 The INFORMATION_SCHEMA COLUMNS Table
19.4 The INFORMATION_SCHEMA STATISTICS Table »
Section Navigation      [Toggle]

19.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
PRIVILEGES Privileges 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 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 12:25am[Delete] [Edit]

This is the equivalent syntax for "all_tab_columns" that is used for Oracle

-Gelomon

Add your own comment.