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 FROMtbl_name
[FROMdb_name
] [LIKE 'wild
']
User Comments
This is the equivalent syntax for "all_tab_columns" that is used for Oracle
-Gelomon
Add your own comment.