Go to the first, previous, next, last section, table of contents.

21 The `INFORMATION_SCHEMA` Information Database

INFORMATION_SCHEMA support is available in MySQL 5.0.2 and later. It provides access to database metadata.

``Metadata'' is data about the data, such as the name of a database or table, the data type of a column, or access privileges. Other terms that sometimes are used for this information are ``data dictionary'' or ``system catalog.''

Here is an example:

mysql> SELECT table_name, table_type, engine
    -> FROM information_schema.tables
    -> WHERE table_schema = 'db5'
    -> ORDER BY table_name DESC;
+------------+------------+--------+
| table_name | table_type | engine |
+------------+------------+--------+
| v56        | VIEW       | NULL   |
| v3         | VIEW       | NULL   |
| v2         | VIEW       | NULL   |
| v          | VIEW       | NULL   |
| tables     | BASE TABLE | MyISAM |
| t7         | BASE TABLE | MyISAM |
| t3         | BASE TABLE | MyISAM |
| t2         | BASE TABLE | MyISAM |
| t          | BASE TABLE | MyISAM |
| pk         | BASE TABLE | InnoDB |
| loop       | BASE TABLE | MyISAM |
| kurs       | BASE TABLE | MyISAM |
| k          | BASE TABLE | MyISAM |
| into       | BASE TABLE | MyISAM |
| goto       | BASE TABLE | MyISAM |
| fk2        | BASE TABLE | InnoDB |
| fk         | BASE TABLE | InnoDB |
+------------+------------+--------+
17 rows in set (0.01 sec)

Explanation: The statement requests a list of all the tables in database db5, in reverse alphabetical order, showing just three pieces of information: the name of the table, its type, and its engine.

INFORMATION_SCHEMA is the ``information database'', the place that stores information about all the other databases that the MySQL server maintains. Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so you won't actually see any file associated with them.

Each MySQL user has the right to access these tables, but only the rows in the tables that correspond to objects for which the user has the proper access privileges.

Advantages of SELECT

The SELECT ... FROM INFORMATION_SCHEMA statement is intended as a more consistent way to provide access to the information provided by the various SHOW statements that MySQL supports (SHOW DATABASES, SHOW TABLES, and so forth). Using SELECT has these advantages, compared to SHOW:

It conforms to Codd's rules. That is, all access is done on tables.
Nobody needs to learn a new statement syntax. Because they already know how SELECT works, they only need to learn the object names.
The implementor need not worry about adding keywords.
There are millions of possible output variations, instead of just one. This provides more flexibility for applications that have varying requirements about what metadata they need.
Migration is easier because every other DBMS does it this way.

However, because SHOW is popular with MySQL employees and users, and because it might be confusing were it to disappear, the advantages of conventional syntax are not a sufficient reason to eliminate SHOW. In fact, there are enhancements to SHOW in MySQL 5.0, too. These are described in section 21.2 Extensions to SHOW Statements.

Standards

The implementation for the INFORMATION_SCHEMA table structures in MySQL follows the ANSI/ISO SQL:2003 standard Part 11 ``Schemata.'' Our intent is approximate compliance with SQL:2003 core feature F021 ``Basic information schema.''

Users of SQL Server 2000 (which also follows the standard) may notice a strong similarity. However, MySQL has omitted many columns that are not relevant for our implementation, and added columns that are MySQL-specific. One such column is the engine column in the INFORMATION_SCHEMA.TABLES table.

Although other DBMSs use a variety of names, like syscat or system, the standard name is INFORMATION_SCHEMA.

In effect, we have a new ``database'' named information_schema, though there is never a need to make a file by that name. It is not possible to issue USE INFORMATION_SCHEMA. The only thing you can do with these tables is SELECT. You cannot update, insert, delete, or even reference them.

Privileges

There is no difference between the current (SHOW) privilege requirement and the SELECT requirement. In either case, you have to have some privilege on an object in order to see information about it.

21.1 `INFORMATION_SCHEMA` Tables

Explanation of following sections

In the following sections, we take the tables and columns that are in INFORMATION_SCHEMA. For each column, there are three pieces of information:

``Standard Name'' indicates the standard SQL name for the column.
``SHOW name'' indicates what the equivalent field name is in the closest SHOW statement, if any.
``Remarks'' provides additional information where applicable. We have marked ``omit'' those columns for which MySQL has no present use. We have omitted these columns, that is, they appear in the standard but not in MySQL. So their presence here is a matter of record only.

To avoid using any name that's reserved in the standard or in DB2 or in SQL Server or in Oracle we changed the names of columns marked ``MySQL extension.'' (For example, we changed COLLATION to TABLE_COLLATION in the TABLES table.) See the list of reserved words near the end of this article: http://www.dbazine.com/gulutzan5.shtml.

The definition for character columns (for example, TABLES.TABLE_NAME), is generally VARCHAR(N) CHARACTER SET utf8 where N is at least 64.

Each section indicates what SHOW statement is equivalent to a SELECT that retrieves information from INFORMATION_SCHEMA, or else that there is no such equivalent statement.

Note: At present, there are some missing columns and some columns out of order. We are working on this and intend to update the documentation as changes are made.

21.1.1 The `INFORMATION_SCHEMA SCHEMATA` Table

A schema is a database, so the SCHEMATA table provides information about databases.

Standard Name	`SHOW` name	Remarks
`CATALOG_NAME`	-	`NULL`
`SCHEMA_NAME`		Database
`SCHEMA_OWNER`		omit
`DEFAULT_CHARACTER_SET_CATALOG`		omit
`DEFAULT_CHARACTER_SET_SCHEMA`		omit
`DEFAULT_CHARACTER_SET_NAME`
`SQL_PATH`		`NULL`

Notes:

For SQL_PATH, we might eventually support something in MySQL 5.x. For now, it's always NULL.

The following statements are equivalent:

SELECT SCHEMA_NAME AS `Database`
  FROM INFORMATION_SCHEMA.SCHEMATA
  [WHERE SCHEMA_NAME LIKE 'wild']

SHOW DATABASES
  [LIKE 'wild']

21.1.2 The `INFORMATION_SCHEMA TABLES` Table

The TABLES table provides information about tables in databases.

Standard Name	`SHOW` name	Remarks
`TABLE_CATALOG`		`NULL`
`TABLE_SCHEMA`	`Table_`...
`TABLE_NAME`	`Table_`...
`TABLE_TYPE`
`SELF_REFERENCING_COLUMN_NAME`		omit
`REFERENCE_GENERATION`		omit
`USER_DEFINED_TYPE_NAME`		omit
`IS_INSERTABLE_INTO`		omit
`IS_TYPED`		omit
`COMMIT_ACTION`		omit
`ENGINE`	`Engine`	MySQL extension
`VERSION`	`Version`	MySQL extension
`ROW_FORMAT`	`Row_format`	MySQL extension
`TABLE_ROWS`	`Rows`	MySQL extension
`AVG_ROW_LENGTH`	`Avg_row_length`	MySQL extension
`DATA_LENGTH`	`Data_length`	MySQL extension
`MAX_DATA_LENGTH`	`Max_data_length`	MySQL extension
`INDEX_LENGTH`	`Index_length`	MySQL extension
`DATA_FREE`	`Data_free`	MySQL extension
`AUTO_INCREMENT`	`Auto_increment`	MySQL extension
`CREATE_TIME`	`Create_time`	MySQL extension
`UPDATE_TIME`	`Update_time`	MySQL extension
`CHECK_TIME`	`Check_time`	MySQL extension
`TABLE_COLLATION`	`Collation`	MySQL extension
`CHECKSUM`	`Checksum`	MySQL extension
`CREATE_OPTIONS`	`Create_options`	MySQL extension
`TABLE_COMMENT`	`Comment`	MySQL extension

Notes:

TABLE_SCHEMA and TABLE_NAME are a single field in a SHOW display, for example Table_in_db1.
TABLE_TYPE should be BASE TABLE or VIEW. If table is temporary, then TABLE_TYPE = TEMPORARY. (There are no temporary views, so this is not ambiguous.)
We have nothing for the table's default character set. TABLE_COLLATION is close, because collation names begin with a character set name.

The following statements are equivalent:

SELECT table_name FROM INFORMATION_SCHEMA.TABLES
  [WHERE table_schema = 'db_name']
  [WHERE|AND table_name LIKE 'wild']

SHOW TABLES
  [FROM db_name]
  [LIKE 'wild']

21.1.3 The `INFORMATION_SCHEMA COLUMNS` Table

The COLUMNS table provides information about columns in tables.

Standard 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_PRECISION_RADIX`		omit
`NUMERIC_SCALE`	`Type`
`DATETIME_PRECISION`		omit
`INTERVAL_TYPE`		omit
`INTERVAL_PRECISION`		omit
`CHARACTER_SET_CATALOG`		omit
`CHARACTER_SET_SCHEMA`		omit
`CHARACTER_SET_NAME`
`COLLATION_CATALOG`		omit
`COLLATION_SCHEMA`		omit
`COLLATION_NAME`	`Collation`
`DOMAIN_NAME`		omit
`UDT_CATALOG`		omit
`UDT_SCHEMA`		omit
`UDT_NAME`		omit
`SCOPE_CATALOG`		omit
`SCOPE_SCHEMA`		omit
`SCOPE_NAME`		omit
`MAXIMUM_CARDINALITY`		omit
`DTD_IDENTIFIER`		omit
`IS_SELF_REFERENCING`		omit
`IS_IDENTITY`		omit
`IDENTITY_GENERATION`		omit
`IDENTITY_START`		omit
`IDENTITY_INCREMENT`		omit
`IDENTITY_MAXIMUM`		omit
`IDENTITY_MINIMUM`		omit
`IDENTITY_CYCLE`		omit
`IS_GENERATED`		omit
`GENERATION_EXPRESSION`		omit
`COLUMN_KEY`	`Key`	MySQL extension
`EXTRA`	`Extra`	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 someday 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's 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 schema_name = 'db_name']
  [AND column_name LIKE 'wild']

SHOW COLUMNS
  FROM tbl_name
  [FROM db_name]
  [LIKE wild]

21.1.4 The `INFORMATION_SCHEMA STATISTICS` Table

The STATISTICS table provides information about table indexes.

Standard Name	`SHOW` name	Remarks
`TABLE_CATALOG`		`NULL`
`TABLE_SCHEMA`		= Database
`TABLE_NAME`	`Table`
`NON_UNIQUE`	`Non_unique`
`INDEX_SCHEMA`		= Database
`INDEX_NAME`	`Key_name`
`TYPE`		omit
`SEQ_IN_INDEX`	`Seq_in_index`
`COLUMN_NAME`	`Column_name`
`COLLATION`	`Collation`
`CARDINALITY`	`Cardinality`
`PAGES`		omit
`FILTER_CONDITION`		omit
`SUB_PART`	`Sub_part`	MySQL extension
`PACKED`	`Packed`	MySQL extension
`NULLABLE`	`Null`	MySQL extension
`INDEX_TYPE`	`Index_type`	MySQL extension
`COMMENT`	`Comment`	MySQL extension

Notes:

There is no standard table for indexes. The preceding list is similar to what SQL Server 2000 returns for sp_statistics, except that we replaced the name QUALIFIER with CATALOG and we replaced the name OWNER with SCHEMA. Clearly, the preceding table and the output from SHOW INDEX are derived from the same parent. So the correlation is already close.

The following statements are equivalent:

SELECT * FROM INFORMATION_SCHEMA.STATISTICS
  WHERE table_name = 'tbl_name'
  [AND schema_name = 'db_name'

SHOW INDEX
  FROM tbl_name
  [FROM db_name]

21.1.5 The `INFORMATION_SCHEMA USER_PRIVILEGES` Table

The USER_PRIVILEGES table provides information about global privileges. This information comes from the mysql.user grant table.

Standard Name	`SHOW` name	Remarks
`GRANTEE`		e.g. 'user'@'host'
`TABLE_CATALOG`		`NULL`
`PRIVILEGE_TYPE`
`IS_GRANTABLE`

Notes:

This is a non-standard table. It takes its values from the mysql.user table.

21.1.6 The `INFORMATION_SCHEMA SCHEMA_PRIVILEGES` Table

The SCHEMA_PRIVILEGES table provides information about schema (database) privileges. This information comes from the mysql.db grant table.

Standard Name	`SHOW` name	Remarks
`GRANTEE`		e.g. 'user'@'host'
`TABLE_CATALOG`		`NULL`
`TABLE_SCHEMA`
`PRIVILEGE_TYPE`
`IS_GRANTABLE`

Notes:

This is a non-standard table. It takes its values from the mysql.db table.

21.1.7 The `INFORMATION_SCHEMA TABLE_PRIVILEGES` Table

The TABLE_PRIVILEGES table provides information about table privileges. This information comes from the mysql.tables_priv grant table.

Standard Name	`SHOW` name	Remarks
`GRANTOR`		omit
`GRANTEE`		e.g. 'user'@'host'
`TABLE_CATALOG`		`NULL`
`TABLE_SCHEMA`
`TABLE_NAME`
`PRIVILEGE_TYPE`
`IS_GRANTABLE`
`WITH_HIERARCHY`		omit

The following statements are not equivalent:

SELECT ... FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES

SHOW GRANTS ...

PRIVILEGE_TYPE can contain one (and only one) of these values: SELECT, INSERT, UPDATE, REFERENCES, ALTER, INDEX, DROP, CREATE VIEW.

21.1.8 The `INFORMATION_SCHEMA COLUMN_PRIVILEGES` Table

The COLUMN_PRIVILEGES table provides information about column privileges. This information comes from the mysql.columns_priv grant table.

Standard Name	`SHOW` name	Remarks
`GRANTOR`		omit
`GRANTEE`		e.g. 'user'@'host'
`TABLE_CATALOG`		`NULL`
`TABLE_SCHEMA`
`TABLE_NAME`
`COLUMN_NAME`
`PRIVILEGE_TYPE`
`IS_GRANTABLE`

Notes:

In the output from SHOW FULL COLUMNS, the privileges are all in one field and in lowercase, for example, select,insert,update,references. In COLUMN_PRIVILEGES, there is one row per privilege, and it's uppercase.
PRIVILEGE_TYPE can contain one (and only one) of these values: SELECT, INSERT, UPDATE, REFERENCES.
If the user has GRANT OPTION privilege, then IS_GRANTABLE should be YES. Otherwise, IS_GRANTABLE should be NO. The output does not list GRANT OPTION as a separate privilege.

The following statements are not equivalent:

SELECT ... FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES

SHOW GRANTS ...

21.1.9 The `INFORMATION_SCHEMA CHARACTER_SETS` Table

The CHARACTER_SETS table provides information about available character sets.

Standard Name	`SHOW` name	Remarks
`CHARACTER_SET_CATALOG`		omit
`CHARACTER_SET_SCHEMA`		omit
`CHARACTER_SET_NAME`	`Charset`
`CHARACTER_REPERTOIRE`		omit
`FORM_OF_USE`		omit
`NUMBER_OF_CHARACTERS`		omit
`DEFAULT_COLLATE_CATALOG`		omit
`DEFAULT_COLLATE_SCHEMA`		omit
`DEFAULT_COLLATE_NAME`	`Default collation`
`DESCRIPION`	`Description`	MySQL extension
`MAXLEN`	`Maxlen`	MySQL extension

Notes:

We have added two non-standard columns corresponding to the Description and Maxlen columns in the output from SHOW CHARACTER SET.

The following statements are equivalent:

SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS
  [WHERE name LIKE 'wild']

SHOW CHARACTER SET
  [LIKE 'wild']

21.1.10 The `INFORMATION_SCHEMA COLLATIONS` Table

The COLLATIONS table provides information about collations for each character set.

Standard Name	`SHOW` name	Remarks
`COLLATION_CATALOG`		omit
`COLLATION_SCHEMA`		omit
`COLLATION_NAME`	`Collation`
`PAD_ATTRIBUTE`		omit
`COLLATION_TYPE`		omit
`COLLATION_DEFINITION`		omit
`COLLATION_DICTIONARY`		omit
`CHARACTER_SET_NAME`		omit MySQL extension
`ID`		omit MySQL extension
`IS_DEFAULT`		omit MySQL extension
`IS_COMPILED`		omit MySQL extension
`SORTLEN`		omit MySQL extension

Notes:

We have added five non-standard columns corresponding to the Charset, Id, Default, Compiled, and Sortlen columns in the output from SHOW COLLATION.

The following statements are equivalent:

SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS
  [WHERE collation_name LIKE 'wild']

SHOW COLLATION
  [LIKE 'wild']

21.1.11 The `INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY` Table

The COLLATION_CHARACTER_SET_APPLICABILITY table indicates what character set is applicable for what collation. The columns are equivalent to the first two display fields that we get from SHOW COLLATION.

Standard Name	`SHOW` name	Remarks
`COLLATION_CATALOG`		omit
`COLLATION_SCHEMA`		omit
`COLLATION_NAME`	`Collation`
`CHARACTER_SET_CATALOG`		omit
`CHARACTER_SET_SCHEMA`		omit
`CHARACTER_SET_NAME`	`Charset`

21.1.12 The `INFORMATION_SCHEMA TABLE_CONSTRAINTS` Table

The TABLE_CONSTRAINTS table describes which tables have constraints.

Standard Name	`SHOW` name	Remarks
`CONSTRAINT_CATALOG`		`NULL`
`CONSTRAINT_SCHEMA`
`CONSTRAINT_NAME`
`TABLE_CATALOG`		omit
`TABLE_SCHEMA`
`TABLE_NAME`
`CONSTRAINT_TYPE`
`IS_DEFERRABLE`		omit
`INITIALLY_DEFERRED`		omit

Notes:

The CONSTRAINT_TYPE value can be UNIQUE, PRIMARY KEY, or FOREIGN KEY.
The UNIQUE and PRIMARY KEY information is about the same as what you get from the Key_name field in the output from SHOW INDEX when the Non_unique field is 0.
The CONSTRAINT_TYPE column can contain one of these values: UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK. This is a CHAR (not ENUM) column. The CHECK value is not available until we support CHECK.

21.1.13 The `INFORMATION_SCHEMA KEY_COLUMN_USAGE` Table

The KEY_COLUMN_USAGE table describes which key columns have constraints.

Standard Name	`SHOW` name	Remarks
`CONSTRAINT_CATALOG`		`NULL`
`CONSTRAINT_SCHEMA`
`CONSTRAINT_NAME`
`TABLE_CATALOG`
`TABLE_SCHEMA`
`TABLE_NAME`
`COLUMN_NAME`
`ORDINAL_POSITION`
`POSITION_IN_UNIQUE_CONSTRAINT`

Notes:

If the constraint is a foreign key, then this is the column of the foreign key, not the column that the foreign key references.
The value of ORDINAL_POSITION is the column's position within the constraint, not the column's position within the table. Column positions are numbered beginning with 1.
The value of POSITION_IN_UNIQUE_CONSTRAINT is NULL for unique and primary-key constraints. For foreign-key constraints, it is the ordinal position in key of the table that is being referenced. For example, suppose that there are two tables name t1 and t3 that have the following definitions:
```
CREATE TABLE t1
(
    s1 INT,
    s2 INT,
    s3 INT,
    PRIMARY KEY(s3)
) ENGINE=InnoDB;

CREATE TABLE t3
(
    s1 INT,
    s2 INT,
    s3 INT,
    KEY(s1),
    CONSTRAINT CO FOREIGN KEY (s2) REFERENCES t1(s3)
) ENGINE=InnoDB;
```
For those two tables, the KEY_COLUMN_USAGE table has two rows:
- One row with CONSTRAINT_NAME='PRIMARY', TABLE_NAME='t1', COLUMN_NAME='s3', ORDINAL_POSITION=1, POSITION_IN_UNIQUE_CONSTRAINT=NULL.
- One row with CONSTRAINT_NAME='CO', TABLE_NAME='t3', COLUMN_NAME='s2', ORDINAL_POSITION=1, POSITION_IN_UNIQUE_CONSTRAINT=1.

21.1.14 The `INFORMATION_SCHEMA ROUTINES` Table

The ROUTINES table provides information about stored routines (both procedures and functions). The ROUTINES table does not include user-defined functions (UDFs) at this time.

The column named ``mysql.proc name'' indicates the mysql.proc table column that corresponds to the INFORMATION_SCHEMA.ROUTINES table column, if any.

Standard Name	`mysql.proc` name	Remarks
`SPECIFIC_CATALOG`		omit
`SPECIFIC_SCHEMA`	`db`	omit
`SPECIFIC_NAME`	`specific_name`
`ROUTINE_CATALOG`		`NULL`
`ROUTINE_SCHEMA`	`db`
`ROUTINE_NAME`	`name`
`MODULE_CATALOG`		omit
`MODULE_SCHEMA`		omit
`MODULE_NAME`		omit
`USER_DEFINED_TYPE_CATALOG`		omit
`USER_DEFINED_TYPE_SCHEMA`		omit
`USER_DEFINED_TYPE_NAME`		omit
`ROUTINE_TYPE`	`type`	`{PROCEDURE\|FUNCTION}`
`DTD_IDENTIFIER`		(data type descriptor)
`ROUTINE_BODY`		`SQL`
`ROUTINE_DEFINITION`	`body`
`EXTERNAL_NAME`		`NULL`
`EXTERNAL_LANGUAGE`	`language`	`NULL`
`PARAMETER_STYLE`		`SQL`
`IS_DETERMINISTIC`	`is_deterministic`
`SQL_DATA_ACCESS`	`sql_data_access`
`IS_NULL_CALL`		omit
`SQL_PATH`		`NULL`
`SCHEMA_LEVEL_ROUTINE`		omit
`MAX_DYNAMIC_RESULT_SETS`		omit
`IS_USER_DEFINED_CAST`		omit
`IS_IMPLICITLY_INVOCABLE`		omit
`SECURITY_TYPE`	`security_type`
`TO_SQL_SPECIFIC_CATALOG`		omit
`TO_SQL_SPECIFIC_SCHEMA`		omit
`TO_SQL_SPECIFIC_NAME`		omit
`AS_LOCATOR`		omit
`CREATED`	`created`
`LAST_ALTERED`	`modified`
`NEW_SAVEPOINT_LEVEL`		omit
`IS_UDT_DEPENDENT`		omit
`RESULT_CAST_FROM_DTD_IDENTIFIER`		omit
`RESULT_CAST_AS_LOCATOR`		omit
`SQL_MODE`	`sql_mode`	MySQL extension
`ROUTINE_COMMENT`	`comment`	MySQL extension
`DEFINER`	`definer`	MySQL extension

Notes:

MySQL calculates EXTERNAL_LANGUAGE thus:
- If mysql.proc.language='SQL', then EXTERNAL_LANGUAGE is NULL
- Otherwise, EXTERNAL_LANGUAGE is what's in mysql.proc.language. However, we don't have external languages yet, so it's always NULL.

21.1.15 The `INFORMATION_SCHEMA VIEWS` Table

The VIEWS table provides information about views in databases.

Standard Name	`SHOW` name	Remarks
`TABLE_CATALOG`		`NULL`
`TABLE_SCHEMA`
`TABLE_NAME`
`VIEW_DEFINITION`
`CHECK_OPTION`
`IS_UPDATABLE`
`INSERTABLE_INTO`		omit

Notes:

There is a new privilege, SHOW VIEW, without which you cannot see the VIEWS table.
The VIEW_DEFINITION column has most of what you see in the Create Table field that SHOW CREATE VIEW produces. Skip the words before SELECT and skip the words WITH CHECK OPTION. For example, if the original statement was:
```
CREATE VIEW v AS
  SELECT s2,s1 FROM t
  WHERE s1 > 5
  ORDER BY s1
  WITH CHECK OPTION;
```
then the view definition is:
```
SELECT s2,s1 FROM t WHERE s1 > 5 ORDER BY s1
```
The CHECK_OPTION column always has a value of NONE.
The IS_UPDATABLE column is YES if the view is updatable, NO if the view is not updatable.

21.1.16 Other `INFORMATION_SCHEMA` Tables

We will add more INFORMATION_SCHEMA tables soon. Particularly, we acknowledge the need for INFORMATION_SCHEMA.PARAMETERS and for INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS and for INFORMATION_SCHEMA.TRIGGERS.

21.2 Extensions to `SHOW` Statements

Some extensions to SHOW statements accompany the implementation of INFORMATION_SCHEMA:

SHOW can be used to get information about the structure of INFORMATION_SCHEMA itself.
Several SHOW statements accept a WHERE clause that provides more flexibility in specifying which rows to display.

These extensions are available beginning with MySQL 5.0.3.

INFORMATION_SCHEMA is an information database, so its name is included in the output from SHOW DATABASES. Similarly, SHOW TABLES can be used with INFORMATION_SCHEMA to obtain a list of its tables:

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                      |
+---------------------------------------+

SHOW COLUMNS and DESCRIBE can display information about the columns in individual INFORMATION_SCHEMA tables.

Several SHOW statement have been extended to allow a WHERE clause:

SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS
SHOW DATABASES
SHOW FUNCTION STATUS
SHOW KEYS
SHOW OPEN TABLES
SHOW PROCEDURE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW VARIABLES

The WHERE clause, if present, is evaluated against the column names displayed by the SHOW statement. For example, the SHOW COLLATION statement produces these output columns:

For example, the SHOW CHARACTER SET statement produces these output columns:

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 |

...

To use a WHERE clause with SHOW CHARACTER SET, you would refer to those column names. As an example, the following statement displays information about character sets for which the default collation contains the string "japanese":

mysql> SHOW CHARACTER SET WHERE `Default collation` LIKE '%japanese%';
+---------+---------------------------+---------------------+--------+
| Charset | Description               | Default collation   | Maxlen |
+---------+---------------------------+---------------------+--------+
| ujis    | EUC-JP Japanese           | ujis_japanese_ci    |      3 |
| sjis    | Shift-JIS Japanese        | sjis_japanese_ci    |      2 |
| cp932   | SJIS for Windows Japanese | cp932_japanese_ci   |      2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci |      3 |
+---------+---------------------------+---------------------+--------+

This statement displays the multi-byte character sets:

mysql> SHOW CHARACTER SET WHERE Maxlen > 1;
+---------+---------------------------+---------------------+--------+
| Charset | Description               | Default collation   | Maxlen |
+---------+---------------------------+---------------------+--------+
| big5    | Big5 Traditional Chinese  | big5_chinese_ci     |      2 |
| ujis    | EUC-JP Japanese           | ujis_japanese_ci    |      3 |
| sjis    | Shift-JIS Japanese        | sjis_japanese_ci    |      2 |
| euckr   | EUC-KR Korean             | euckr_korean_ci     |      2 |
| gb2312  | GB2312 Simplified Chinese | gb2312_chinese_ci   |      2 |
| gbk     | GBK Simplified Chinese    | gbk_chinese_ci      |      2 |
| utf8    | UTF-8 Unicode             | utf8_general_ci     |      3 |
| ucs2    | UCS-2 Unicode             | ucs2_general_ci     |      2 |
| cp932   | SJIS for Windows Japanese | cp932_japanese_ci   |      2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci |      3 |
+---------+---------------------------+---------------------+--------+

Go to the first, previous, next, last section, table of contents.