Home / Database Structure / Question No: 200

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:

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'world';

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

SHOW DATABASES;

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.

SHOW DATABASES LIKE 'm%';

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.