SHOW COLUMN Statement

Description

Displays the column information of a table. You can use the LIKE clause to search the column names matching it. If you use the WHERE clause, you can search column names with more general terms like, "General Considerations for All SHOW Statements."  If you use the FULL keyword, the additional information of a column will be displayed as follows:

SHOW FIELDS is the same command as SHOW COLUMNS.

The DESCRIBE(abbreviated DESC) statement and the EXPLAIN statement provide similar information to SHOW COLUMNS.

Syntax

SHOW COLUMNS {FROM | IN} tbl_name [LIKE 'pattern' | WHERE expr]

Example

The following is the result of a query for the demodb.

SHOW COLUMNS FROM athlete;

  Field                 Type                  Null                  Key                   Default               Extra

====================================================================================================================================

  'code'                'INTEGER'             'NO'                  'PRI'                 NULL                  'auto_increment'

  'name'                'STRING(40)'          'NO'                  ''                    NULL                  ''

  'gender'              'CHAR(1)'             'YES'                 ''                    NULL                  ''

  'nation_code'         'CHAR(3)'             'YES'                 ''                    NULL                  ''

  'event'               'STRING(30)'          'YES'                 ''                    NULL                  ''

 

SHOW COLUMNS FROM athlete LIKE '%c%';

  Field                 Type                  Null                  Key                   Default               Extra

====================================================================================================================================

  'code'                'INTEGER'             'NO'                  'PRI'                 NULL                  'auto_increment'

  'nation_code'         'CHAR(3)'             'YES'                 ''                    NULL                  ''

 

SHOW COLUMNS FROM athlete  WHERE "type" = 'INTEGER' and "key"='PRI' AND extra='auto_increment';

  Field                 Type                  Null                  Key                   Default               Extra

====================================================================================================================================

  'code'                'INTEGER'             'NO'                  'PRI'                 NULL                  'auto_increment'