MySQL/Language/Browsing the databases

INFORMATION_SCHEMA

information_schema is a virtual database provided by MySQL 5 and later, that contains metadata about the server and the databases.

You can't modify structure and data of information_schema. You can only query the tables.

Many information_schema tables provide the same data you can retrieve with a SHOW statement. While using SHOW commands is faster (the server responds much faster and you type less characters), the information_schema provides a more flexible way to obtain and organize the metadata.

List databases

The INFORMATION_SCHEMA table containing the databases information is SCHEMATA.

The mysqlshow command line tool (DOS/Unix) can be used instead. You can't show databases if the server has been started with the --skip-all-databases option.

If you don't have the 'SHOW DATABASES' privilege, you'll only see databases on which you have some permissions.

The following SQL commands provide information about the databases located on the current server.

Show all databases:

 SHOW DATABASES;

The SCHEMA keywords can be used in place of DATABASES. MySQL doesn't support standard SQL SCHEMAs, so SCHEMA is a synonym of database. It has been added for compatibility with other DBMSs.

Add a filter on the databases names

 SHOW DATABASES LIKE 'pattern';

The LIKE operator here works as in normal SELECTs or DML statements. So you can list all databases whose name starts with 'my':

 SHOW DATABASES LIKE 'my%';

Add complex filters

You can add more complex filters using the WHERE clause:

 SHOW DATABASES WHERE conditions;

WHERE clause allows you to use regular expressions, '=', '<' and '>' operators, string functions or other useful expressions to filter the records returned by SHOW DATABASES.

List tables and views

The following SQL commands provide information about the tables and views contained in a database. The INFORMATION_SCHEMA tables containing this information are `TABLES` and `VIEWS`.

Since the following statements provide very little information about views, if you need to get metadata about them you'll probably prefer to query the VIEWS table.

The mysqlshow command line tool can be used instead.

Show all tables

 USE `database`;
 SHOW TABLES;

 SHOW TABLES FROM `database`;

The 2 forms shown above are equivalent.

Apply a filter

You can apply a filter to the tables names, to show only tables whose name match a pattern. You can use the LIKE operators, as you do in SELECTs or in the DML statements:

 SHOW TABLES LIKE `pattern`;

Also, you can apply a more complex filter to any column returned by the SHOW TABLES command using the WHERE clause:

 SHOW TABLES WHERE condition;

(see below)

Extra info

By default, SHOW TABLES returns only one column containing the name of the table. You can get extra information by using the FULL keyword:

 SHOW FULL TABLES;

This will add a column called `Table_type`. This can have three values: 'BASE TABLE' for tables, 'VIEW' for views and 'SYSTEM VIEW' for special tables created by the server (normally used only INFORMATION_SCHEMA tables).

So you can only list tables:

 SHOW FULL TABLES WHERE `Table_type`='BASE TABLE';

Or, you can only list views:

 SHOW FULL TABLES WHERE `Table_type`='VIEW';

Show only open tables

You can get a list of the non-temporary tables (not views) which are open in the cache:

 SHOW OPEN TABLES;

This command has the same parameters as SHOW TABLES, except for FULL (useless in this case). You can't get this information from the INFORMATION_SCHEMA.

List fields

The following SQL commands provide information about the columns in a table or in a view. The INFORMATION_SCHEMA table containing this information is COLUMNS.

The mysqlshow command line tool can be used instead.

DESCRIBE

 DESCRIBE `table`;
 DESCRIBE `database`.`table`;
 DESCRIBE `table` 'filter';

DESC can be used as a shortcut for DESCRIBE.

'filter' can be a column name. If a column name is specified, only that column will be shown. If 'filter' contains the '%' or the '_' characters, it will be evaluated as a LIKE condition. For example, you can list all fields which start with 'my':

 DESC `table` 'my%';

EXPLAIN

A synonym is:

 EXPLAIN `table`;

SHOW FIELDS

Another synonym is:

 SHOW FIELDS FROM `table`;

SHOW COLUMNS

Another synonym is:

 SHOW COLUMNS FROM `table`;

-- possible clauses:
 SHOW COLUMNS FROM `table` FROM `database`;
 SHOW COLUMNS FROM `table` LIKE 'pattern';
 SHOW COLUMNS FROM `table` WHERE condition;

FIELDS and COLUMNS are synonyms. EXPLAIN is a synonym of SHOW COLUMNS / FIELDS too, but it doesn't support all of its clauses.

A databases name can be specified both in the form

 SHOW COLUMNS FROM `table` FROM `database`;

both:

 SHOW COLUMNS FROM `database`.`table`;

Extra info

Using the FULL keyword, extra info can be retried: the columns' collation, privileges you have on the column and the comment.

Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
...	...	...	...	...	...	...	...	...

List indexes

The following SQL commands provide information about the indexes in a table. Information about keys is contained in the `COLUMNS` table in the INFORMATION_SCHEMA.

The mysqlshow -k command line tool can be used instead.

 SHOW INDEX FROM `TABLE`;
 SHOW INDEX FROM `TABLE` FROM `databases`;

The KEYS reserved word can be used as a synonym of INDEX. No other clauses are provided.

Result example:

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
Table1	0	PRIMARY	1	id	A	19	NULL	NULL		BTREE

Remark: with phpMyAdmin it's easy to create the same index multiple times, which slows the requests.

To remove an index:

DROP INDEX `date_2` on `Table1`