The INFORMATION_SCHEMA.COLUMNS Table : INFORMATION_SCHEMA « Data Dictionary « MySQL Tutorial

1. MySQL Tutorial
2. Data Dictionary
3. INFORMATION_SCHEMA

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']