20.3. Using SHOW and DESCRIBE to Obtain MetadataMySQL supports a set of SHOW statements that each return one kind of metadata. This section describes a few of them.
For some SHOW statements, you can give a LIKE clause to perform a pattern-match operation that determines which rows to display. SHOW DATABASES, SHOW TABLES, and SHOW COLUMNS support this feature. For example: mysql> SHOW DATABASES LIKE 'm%'; +---------------+ | Database (m%) | +---------------+ | menagerie | | mysql | +---------------+ SHOW also supports the use of a WHERE clause. As with the LIKE clause, WHERE determines which rows to display, but WHERE is more flexible because you can use any kind of test, not just a pattern match: mysql> SHOW COLUMNS FROM Country WHERE `Default` IS NULL; +----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+-------+ | IndepYear | smallint(6) | YES | | NULL | | | LifeExpectancy | float(3,1) | YES | | NULL | | | GNP | float(10,2) | YES | | NULL | | | GNPOld | float(10,2) | YES | | NULL | | | HeadOfState | char(60) | YES | | NULL | | | Capital | int(11) | YES | | NULL | | +----------------+-------------+------+-----+---------+-------+ In the preceding statement, the column name (Default) must be given as a quoted identifier because it is a reserved word. SHOW statements are available for metadata other than for databases, tables, and columns. For example, SHOW CHARACTER SET displays the available character sets and SHOW COLLATION displays the collations for each character set: mysql> SHOW CHARACTER SET; +----------+-----------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+-----------------------------+---------------------+--------+ | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | latin1 | ISO 8859-1 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | ... mysql> SHOW COLLATION; +----------------------+----------+-----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +----------------------+----------+-----+---------+----------+---------+ | big5_chinese_ci | big5 | 1 | Yes | Yes | 1 | | big5_bin | big5 | 84 | | Yes | 1 | | dec8_swedish_ci | dec8 | 3 | Yes | | 0 | | dec8_bin | dec8 | 69 | | | 0 | | cp850_general_ci | cp850 | 4 | Yes | | 0 | | cp850_bin | cp850 | 80 | | | 0 | | hp8_english_ci | hp8 | 6 | Yes | | 0 | ... DESCRIBE, another metadata-display statement, is equivalent to SHOW COLUMNS. The following two statements display the same information: DESCRIBE table_name; SHOW COLUMNS FROM table_name; However, whereas SHOW COLUMNS supports the optional FULL keyword, DESCRIBE does not. Although the contents of INFORMATION_SCHEMA and its tables typically are accessed using SELECT, it's also possible to use SHOW and DESCRIBE with INFORMATION_SCHEMA, just as with any other database. For example, the output from SHOW DATABASES includes INFORMATION_SCHEMA and SHOW TABLES lists its tables: mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | menagerie | | mysql | | test | | world | +--------------------+ mysql> SHOW TABLES FROM INFORMATION_SCHEMA; +---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | SCHEMATA | | TABLES | | COLUMNS | | CHARACTER_SETS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | | ROUTINES | | STATISTICS | | VIEWS | | USER_PRIVILEGES | | SCHEMA_PRIVILEGES | | TABLE_PRIVILEGES | | COLUMN_PRIVILEGES | | TABLE_CONSTRAINTS | | KEY_COLUMN_USAGE | | TRIGGERS | +---------------------------------------+ DESCRIBE shows the column definitions for any INFORMATION_SCHEMA table: mysql> DESCRIBE INFORMATION_SCHEMA.CHARACTER_SETS; +----------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+-------------+------+-----+---------+-------+ | CHARACTER_SET_NAME | varchar(64) | NO | | | | | DEFAULT_COLLATE_NAME | varchar(64) | NO | | | | | DESCRIPTION | varchar(60) | NO | | | | | MAXLEN | bigint(3) | NO | | 0 | | +----------------------+-------------+------+-----+---------+-------+ |