14.6. Obtaining View Metadata | MySQL 5.0 Certification Study Guide

The INFORMATION_SCHEMA database has a VIEWS table that contains view metadata (information about views). For example, to display information about the world.CityView view that was created earlier, use this statement:

 mysql> SELECT * FROM INFORMATION_SCHEMA.VIEWS     -> WHERE TABLE_NAME = 'CityView'     -> AND TABLE_SCHEMA = 'world'\G *************************** 1. row ***************************   TABLE_CATALOG: NULL    TABLE_SCHEMA: world      TABLE_NAME: CityView VIEW_DEFINITION: select `world`.`City`.`ID` AS `ID`,`world`.`City`.`Name`                  AS `Name` from `world`.`City`    CHECK_OPTION: NONE    IS_UPDATABLE: YES

INFORMATION_SCHEMA also has a TABLES table that contains view metadata.

For further information about INFORMATION_SCHEMA, see Chapter 20, "Obtaining Database Metadata."

MySQL also supports a family of SHOW statements that display metadata. To display the definition of a view, use the SHOW CREATE VIEW statement:

 mysql> SHOW CREATE VIEW CityView\G *************************** 1. row ***************************        View: CityView Create View: CREATE ALGORITHM=UNDEFINED VIEW `world`.`CityView` AS              select `world`.`City`.`ID` AS `ID`,`world`.`City`.`Name`              AS `Name` from `world`.`City`

Some statements in MySQL that originally were designed to display base table information have been extended so that they also work with views:

DESCRIBE and SHOW COLUMNS
SHOW TABLE STATUS
SHOW TABLES

By default, SHOW TABLES lists only the names of tables and views. MySQL 5 has a SHOW FULL TABLES variant that displays a second column. The values in the column are BASE TABLE or VIEW to indicate what kind of object each name refers to:

 mysql> SHOW FULL TABLES FROM world; +------------------+------------+ | Tables_in_world  | Table_type | +------------------+------------+ | City             | BASE TABLE | | CityView         | VIEW       | | Country          | BASE TABLE | | CountryLangCount | VIEW       | | CountryLanguage  | BASE TABLE | | CountryPop       | BASE TABLE | | EuropePop        | VIEW       | | LargePop         | VIEW       | +------------------+------------+