Chapter 13. Understanding the INFORMATION_SCHEMA


  • What tables exist in the INFORMATION_SCHEMA

  • How to retrieve information on your schemas and tables

  • How to list core MySQL metadata used for database objects

  • How to retrieve instrumentation on a running MySQL instance

  • What extensions to INFORMATION_SCHEMA exist

The ANSI Standard SQL (e.g, SQL:2003 and SQL:2008) defines the support for a level of database metadata using the concept of INFORMATION_SCHEMA. MySQL starting with version 5.0 has implemented the INFORMATION_SCHEMA, often referred to as I_S, to provide a level of SQL access to information previously found in the popular but not SQL standard SHOW commands. The metadata is a form of data dictionary that provides the user access to various information including database objects such as TABLES, COLUMNS, and VIEWS; schema metadata such as CHARACTER_SETS; and internal MySQL operations including GLOBAL_STATUS, GLOBAL_VARIABLES, and PROCESSLIST.

This chapter steps through the INFORMATION_SCHEMA tables from versions 5.0, 5.1, and the 5.4 alpha release. It also gives an example of the INFORMATION_SCHEMA extensions possible using the InnoDB Plugin 1.0.4 and greater, which is available separately and now included in MySQL starting with version 5.1.38.


The SHOW command can be used to retrieve information from the INFORMATION_SCHEMA tables. As you can see, this 5.1 schema contains a number of tables, which are discussed in detail in this chapter: ...

Get Expert PHP and MySQL® now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.