The COLUMNS table provides information about columns in tables.
mysql> desc INFORMATION_SCHEMA.COLUMNS; +--------------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+--------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | YES | | NULL | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | COLUMN_NAME | varchar(64) | NO | | | | | ORDINAL_POSITION | bigint(21) | NO | | 0 | | | COLUMN_DEFAULT | longtext | YES | | NULL | | | IS_NULLABLE | varchar(3) | NO | | | | | DATA_TYPE | varchar(64) | NO | | | | | CHARACTER_MAXIMUM_LENGTH | bigint(21) | YES | | NULL | | | CHARACTER_OCTET_LENGTH | bigint(21) | YES | | NULL | | | NUMERIC_PRECISION | bigint(21) | YES | | NULL | | | NUMERIC_SCALE | bigint(21) | YES | | NULL | | | CHARACTER_SET_NAME | varchar(64) | YES | | NULL | | | COLLATION_NAME | varchar(64) | YES | | NULL | | | COLUMN_TYPE | longtext | NO | | | | | COLUMN_KEY | varchar(3) | NO | | | | | EXTRA | varchar(20) | NO | | | | | PRIVILEGES | varchar(80) | NO | | | | | COLUMN_COMMENT | varchar(255) | NO | | | | +--------------------------+--------------+------+-----+---------+-------+ 19 rows in set (0.02 sec)
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']