[MySQL] : Information schema
INFORMATION_SCHEMA Tables
INFORMATION_SCHEMA 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 and system catalog.
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.
Example of a statement that retrieves information from INFORMATION_SCHEMA:
mysql> SELECT table_name, table_type, engine
FROM information_schema.tables
WHERE table_schema = ‘db5’
ORDER BY table_name DESC;
The statement requests a list of all the tables in database db5, in reverse alphabetic order, showing just three pieces of information: the name of the table, its type, and its storage engine.
SCHEMATA Table:
This table provides information about databases.
TABLES Table:
This table provides information about tables in databases.
STATISTICS Table:
This table provides information about table indexes.
USER_PRIVILEGES Table:
This table provides information about global privileges. This information comes from the mysql.user grant table.
SCHEMA_PRIVILEGES Table:
This table provides information about schema (database) privileges. This information comes from the mysql.db grant table.
TABLE_PRIVILEGES Table:
This table provides information about table privileges. This information comes from the mysql.tables_priv grant table.
COLUMN_PRIVILEGES Table:
This table provides information about column privileges. This information comes from the mysql.columns_priv grant table.
TABLE_CONSTRAINTS Table:
This table describes which tables have constraints.
REFERENTIAL_CONSTRAINTS Table:
This table provides information about foreign keys. This table was added in MySQL 5.1.10.
ROUTINES Table:
This table provides information about stored routines (both procedures and functions). his information comes from the mysql.proc table.
VIEWS Table:
This table provides information about views in databases. You must have the SHOW VIEW privilege to access this table.
TRIGGERS Table:
This table provides information about triggers. You must have the SUPER privilege to access this table.
PROFILING Table:
This table provides statement profiling information. Its contents correspond to the information produced by the SHOW PROFILES and SHOW PROFILE statements. The table is empty unless the profiling session variable is set to 1.
ENGINES Table:
This table provides information about storage engines.
PARTITIONS Table:
This table provides information about table partitions.
GLOBAL_STATUS and SESSION_STATUS Tables:
This tables provide information about server status variables. Their contents correspond to the information produced by the SHOW GLOBAL STATUS and SHOW SESSION STATUS statements. This tables were added in MySQL 5.1.12.
GLOBAL_VARIABLES and SESSION_VARIABLES Tables:
This tables provide information about server status variables. Their contents correspond to the information produced by the SHOW GLOBAL VARIABLES and SHOW SESSION VARIABLES statements. This tables were added in MySQL 5.1.12.
PROCESSLIST Table:
The PROCESSLIST table provides information about which threads are running.
EVENTS Table:
The EVENTS table provides information about scheduled events.