20.2. Using INFORMATION_SCHEMA to Obtain Metadata

20.2. Using INFORMATION_SCHEMA to Obtain Metadata

The INFORMATION_SCHEMA database serves as a central repository for database metadata. It is a "virtual database" in the sense that it is not stored on disk anywhere, but it contains tables like any other database, and the contents of its tables can be accessed using SELECT like any other tables. Furthermore, you can use SELECT to obtain information about INFORMATION_SCHEMA itself. For example, to list the names of its tables, use the following statement:

 mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES     -> WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'     -> ORDER BY TABLE_NAME; +---------------------------------------+ | TABLE_NAME                            | +---------------------------------------+ | 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                                 | +---------------------------------------+ 

The tables shown in that list contain the following types of information:

• CHARACTER_SETS

  Information about available character sets

• COLLATIONS

  Information about collations for each character set

• COLLATION_CHARACTER_SET_APPLICABILITY

  Information about which character set applies to each collation

• COLUMNS

  Information about columns in tables

• COLUMN_PRIVILEGES

  Information about column privileges held by MySQL user accounts

• KEY_COLUMN_USAGE

  Information about constraints on key columns

• ROUTINES

  Information about stored procedures and functions

• SCHEMATA

  Information about databases

• SCHEMA_PRIVILEGES

  Information about database privileges held by MySQL user accounts

• STATISTICS

  Information about table indexes

• TABLES

  Information about tables in databases

• TABLE_CONSTRAINTS

  Information about constraints on tables

• TABLE_PRIVILEGES

  Information about table privileges held by MySQL user accounts

• trIGGERS

  Information about triggers in databases

• USER_PRIVILEGES

  Information about global privileges held by MySQL user accounts

• VIEWS

  Information about views in databases

To display the names of the columns in a given INFORMATION_SCHEMA table, use a statement of the following form, where the TABLE_NAME comparison value names the table in which you're interested:

 mysql> SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS     -> WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'     -> AND TABLE_NAME = 'VIEWS'; +-----------------+ | COLUMN_NAME     | +-----------------+ | TABLE_CATALOG   | | TABLE_SCHEMA    | | TABLE_NAME      | | VIEW_DEFINITION | | CHECK_OPTION    | | IS_UPDATABLE    | +-----------------+ 

The names of the INFORMATION_SCHEMA database, its tables, and columns are not case sensitive:

 mysql> SELECT column_name FROM information_schema.columns     -> WHERE table_schema = 'information_schema'     -> AND table_name = 'views'; +-----------------+ | column_name     | +-----------------+ | TABLE_CATALOG   | | TABLE_SCHEMA    | | TABLE_NAME      | | VIEW_DEFINITION | | CHECK_OPTION    | | IS_UPDATABLE    | +-----------------+ 

This study guide does not go into any detail about the columns in INFORMATION_SCHEMA tables. For a more comprehensive description of each table's columns, see the MySQL Reference Manual.

When you retrieve metadata from INFORMATION_SCHEMA by using SELECT statements, you have the freedom to use any of the usual SELECT features that you expect:

• You can specify in the select list which columns to retrieve.

• You can restrict which rows to retrieve by specifying conditions in a WHERE clause.

• You can group or sort the results with GROUP BY or ORDER BY.

• You can use joins, unions, and subqueries.

• You can retrieve the result of an INFORMATION_SCHEMA query into another table with CREATE TABLE ... SELECT or INSERT ... SELECT. This enables you to save the result and use it in other statements later.

The following examples demonstrate how to exploit various features of SELECT to pull out information in different ways from INFORMATION_SCHEMA:

• Display the storage engines used for the tables in a given database:

   SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'world'; 

  
  

• Find all the tables that contain SET columns:

   SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'set'; 

  
  

• Display the default collation for each character set:

   SELECT CHARACTER_SET_NAME, COLLATION_NAME  FROM INFORMATION_SCHEMA.COLLATIONS WHERE IS_DEFAULT = 'Yes'; 

  
  

• Display the number of tables in each database:

   SELECT TABLE_SCHEMA, COUNT(*) FROM INFORMATION_SCHEMA.TABLES; GROUP BY TABLE_SCHEMA; 

  
  

INFORMATION_SCHEMA is read-only. Its tables cannot be modified with statements such as INSERT, DELETE, or UPDATE. If you try, an error occurs:

 mysql> DELETE FROM INFORMATION_SCHEMA.VIEWS; ERROR 1288 (HY000): The target table VIEWS of the DELETE is not updatable