# 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:
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

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

### Add complex filters

You can add more complex filters using the WHERE clause:
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

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:
Also, you can apply a more complex filter to any column returned by the SHOW TABLES command using the WHERE clause:
(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:
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:
Or, you can only list views:

### Show only open tables

You can get a list of the non-temporary tables (not views) which are open in the cache:
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

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':

### EXPLAIN

A synonym is:

### SHOW FIELDS

Another synonym is:

### SHOW COLUMNS

Another synonym is:
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
both:

### 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.
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: