7.5. Obtaining Database Metadata


The INFORMATION_SCHEMA database has a SCHEMATA table that contains database metadata (information about databases). For example, to display information about the world database, use this statement:

 mysql> SELECT * FROM INFORMATION_SCHEMA.SCHEMATA     -> WHERE SCHEMA_NAME = 'world'\G *************************** 1. row ***************************               CATALOG_NAME: NULL                SCHEMA_NAME: world DEFAULT_CHARACTER_SET_NAME: latin1     DEFAULT_COLLATION_NAME: latin1_swedish_ci                   SQL_PATH: NULL 

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

MySQL also supports a family of SHOW statements that display metadata. The statement that lists database names is SHOW DATABASES:

 mysql> SHOW DATABASES; +--------------------+ | Database           | +--------------------+ | information_schema | | menagerie          | | mysql              | | test               | | world              | +--------------------+ 

The information_schema database should always be listed by SHOW DATABASES. The mysql and test databases are created during MySQL installation, so you're likely to see both of them in the output from the statement as well. The mysql database contains the grant tables and should always be present because the grant tables contain user account information that the server uses to control access to the databases. The test database will be present unless someone has removed it.

SHOW DATABASES can take a LIKE 'pattern' clause. With LIKE, the statement performs a pattern-matching operation and displays information only about databases with names that match the pattern. Patterns are discussed in Section 10.3.2, "Using LIKE for Pattern Matching."

 mysql> SHOW DATABASES LIKE 'm%'; +---------------+ | Database (m%) | +---------------+ | menagerie     | | mysql         | +---------------+ 

The output of the SHOW DATABASES statement depends on whether you have the SHOW DATABASES privilege. If you have the privilege, the statement shows the names of all existing databases. Otherwise, it shows only those databases to which you have access.

SHOW CREATE DATABASE shows the CREATE DATABASE statement that creates a database:

 mysql> SHOW CREATE DATABASE world\G *************************** 1. row ***************************        Database: world Create Database: CREATE DATABASE `world`                  /*!40100 DEFAULT CHARACTER SET latin1 */ 



MySQL 5 Certification Study Guide
MySQL 5.0 Certification Study Guide
ISBN: 0672328127
EAN: 2147483647
Year: 2006
Pages: 312

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net