What is INFORMATION_SCHEMA in a SQL Server

Chapter 22. The database

gives access to database metadata.

Metadata is information about data, for example the name of a database or table, the data type of a column, or access rights. Sometimes this information is also called a Data dictionary or System catalog designated.

is the information database, i.e. the place where information about all other databases maintained on the relevant MySQL server is stored. There are a number of read-only tables in the. Since these are not really base tables but views, you will not see any files associated with them.

An example:

mysql> -> -> -> + ------------ + ------------ + -------- + | 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 | | course | 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 asks for a list of all tables in the database in reverse alphabetical order, with only three pieces of information displayed at a time: the name of the table, its type, and its storage engine.

Every MySQL user has the right to access these tables, but only to those table rows that refer to objects for which he has the correct access rights.

The statement is intended as a more consistent means of providing access to the information given by the various statements supported by MySQL (, etc.). Using has the following advantages over:

  • It corresponds to Codd's rules: All access goes to tables.

  • Nobody has to learn a new instruction syntax. Since everyone already knows how it works, all that needs to be learned is the object names.

  • The implementer does not have to worry about keywords.

  • Millions of variations are possible for the output, not just one. This gives you more flexibility for applications that repeatedly need different metadata.

  • Migration is easier because all other DBMS work in the same way.

However, because MySQL is so popular with employees and users, and eliminating this statement could cause confusion, the advantages of the conventional syntax are not enough reason to forego it altogether. Instead, there are even improvements to MySQL 5.1. These are described in more detail in Section 22.22, “Extensions to Instructions”.

There is no difference between the permissions required for statements and those required to query data from the. In both cases you must have some kind of authorization for an object in order to display information about it.

The implementation of the table structures in MySQL corresponds to the ANSI / ISO SQL: 2003 Standard Part 11 Schemes. Our goal is to use the SQL: 2003 core feature F021 as much as possible Basic information schema correspond to.

SQL Server 2000 users (which also follows the standard) may notice strong similarities. However, MySQL has left out many columns that are not relevant to our implementation and added other, MySQL-specific columns instead. This includes the column in the table.

The other DBMS use different names, such as or, but the default name is.

Ultimately, we have a database named in front of us, even if the server does not create a database directory for it. It is possible to set the standard database with an instruction, but the table contents can only be read. Insertions, changes or deletions in the tables are excluded.

The following sections describe the tables and columns of. There are three pieces of information for each column:

  • The "Standard Name" indicates the SQL standard name of the column.

  • "Name" is the corresponding field name in the closest statement (if any).

  • "Remarks" are any additional information. If this field has the value, it means that the value of the column is always.

In order not to use names that are reserved in the standard or in DB2, SQL Server or Oracle, we have changed the names of the columns marked as “MySQL extension”. (For example, in the table it became a.) See also the list of reserved words at the end of this article: http://www.dbazine.com/gulutzan5.shtml.

The definition for character columns (for example) is generally, where is at least 64.

Each section specifies which instruction corresponds to an instruction that queries information from the, if such an instruction exists.

Note: Currently some columns are still missing and others are in the wrong order. We are working on it and will update the documentation if there are any changes.


This is a translation of the MySQL Reference Manual located on dev.mysql.com. The original reference manual is in English, and this translation is not necessarily as up-to-date as the English edition. This German-language manual covers MySQL up to version 5.1.