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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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.