MySQL information schema database

From Cosmin's Wiki

Jump to: navigation, search

Home > MySQL > MySQL information schema database



Contents

Description

MySQL 5.02 and later includes an information_schema database. The MySQL information_schema is a basic implementation of the INFORMATION_SCHEMA schema defined in the SQL Standard (ISO/IEC 9075). The purpose of this database is to provide data describing the databases and their constituent objects: metadata.

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 there are no files associated with them.

In effect, we have a database named information_schema, although the server does not create a database directory with that name. It is possible to select information_schema as the default database with a USE statement, but it is possible only to read the contents of tables. You cannot insert into them, update them, or delete from them.

Metadata is available only for those objects that are accessible to the current user. The metadata is automatically maintained by the server, and the information_schema database is automatically created upon MySQL installation.

In MySQL versions prior to 5.0.2, virtually the only way to obtain metadata was through the MySQL specific SHOW syntax.

The tables in the information_schema database are:

mysql> use information_schema;
Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| KEY_COLUMN_USAGE                      |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
16 rows in set (0.00 sec)


Examples

In this section I will show some simple select statements for obtaining different pieces of information that I have found useful over time:

MySQL: List a database's table information

MySQL: List all tables containing a given column name