- 19 INFORMATION_SCHEMA Tables
- 19.1 The INFORMATION_SCHEMA CHARACTER_SETS Table
- 19.2 The INFORMATION_SCHEMA COLLATIONS Table
- 19.3 The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table
- 19.4 The INFORMATION_SCHEMA COLUMNS Table
- 19.5 The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table
- 19.6 The INFORMATION_SCHEMA ENGINES Table
- 19.7 The INFORMATION_SCHEMA EVENTS Table
- 19.8 The INFORMATION_SCHEMA FILES Table
- 19.9 The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables
- 19.10 The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables
- 19.11 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
- 19.12 The INFORMATION_SCHEMA PARTITIONS Table
- 19.13 The INFORMATION_SCHEMA PLUGINS Table
- 19.14 The INFORMATION_SCHEMA PROCESSLIST Table
- 19.15 The INFORMATION_SCHEMA PROFILING Table
- 19.16 The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table
- 19.17 The INFORMATION_SCHEMA ROUTINES Table
- 19.18 The INFORMATION_SCHEMA SCHEMATA Table
- 19.19 The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table
- 19.20 The INFORMATION_SCHEMA STATISTICS Table
- 19.21 The INFORMATION_SCHEMA TABLES Table
- 19.22 The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table
- 19.23 The INFORMATION_SCHEMA TABLE_PRIVILEGES Table
- 19.24 The INFORMATION_SCHEMA TRIGGERS Table
- 19.25 The INFORMATION_SCHEMA USER_PRIVILEGES Table
- 19.26 The INFORMATION_SCHEMA VIEWS Table
- 19.27 Other INFORMATION_SCHEMA Tables
- 19.28 Extensions to SHOW Statements
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, theTypedisplay includes values from several differentCOLUMNScolumns.ORDINAL_POSITIONis necessary because you might want to sayORDER BY ORDINAL_POSITION. UnlikeSHOW,SELECTdoes not have automatic ordering.CHARACTER_OCTET_LENGTHshould be the same asCHARACTER_MAXIMUM_LENGTH, except for multi-byte character sets.CHARACTER_SET_NAMEcan be derived fromCollation. For example, if you saySHOW FULL COLUMNS FROM t, and you see in theCollationcolumn a value oflatin1_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
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.