MySQL INFORMATION_SCHEMA Examples

In version 5.0, MySQL implemented the INFORMATION_SCHEMA database, which is visible as a normal database. Although its behavior and functionality are similar to a normal database, the information contained in the database is not all regular data.

Here is the best way I can describe the MySQL INFORMATION_SCHEMA database. It is a database containing information about other databases. It is available in every MySQL instance and is used to store metadata information about all other databases in the server. It is also called the system catalog or data dictionary.

Like all the MySQL databases, the information in the INFORMATION_SCHEMA database gets stored in read-only tables. However, in actuality, they are views and not base MySQL tables. In that sense, you can not perform MySQL triggers against the tables stored in the database, nor are there any files associated with them.

NOTE: You will also not find a directory in the name of INFORMATION_SCHEMA.

Despite all that, the INFORMATION_SCHEMA database is the perfect place to query information about other databases stored on the server. This tutorial aims to provide you with an overview of the INFORMATION_SCHEMA database and give you a few examples of using the database.

Basic Information about INFORMATION_SCHEMA

As of MySQL 5.0 and above, if you execute the show databases, the information _schema database is visible and can be used as a normal database.

One can use the INFORMATION_SCHEMA database simply by using the MySQL use query as:

mysql > USE information_schema;

Once in the database, you can view the tables stored using the command as:

The above output shows some of the tables stored in the information_schema database. Remember that they are not actual tables and therefore cannot be modified. Instead, they are stored in the memory and dropped once the MySQL server is shut down. Upon reboot, the information_schema is rebuilt and populated with the existing data on the server.

Select from Information_schema

To view the information stored in the information_schema database, you can use the select statement. For example, to list information in the engines table, the command as:

mysql> SELECT * FROM information_schema.engines;

This will dump the data stored in the table as shown:

Example 1 – Show Largest Tables

The example below shows how to use the INFORMATION_SCHEMA to show the largest databases on the server.

Upon executing the above query, you will get all the tables in your size, starting from the largest to the smallest.

Here is an example output:

Example 2 – Show privileges

Using the TABLE_PRIVILEGES in the information schema database, we can get the privileges on the MySQL server. Some of the columns in this table are:

GRANTEE – This shows the MySQL account to which the privilege is granted. This is usually in the format of username@hostname.
TABLE_CATALOG – Contains the name of the catalog to which the table belongs to. The value is def by default.
TABLE_SCHEMA – The name of the database to which the table belongs.
TABLE_NAME – The name of the table.
IS_GRANTEE – Boolean value if the user has GRANT privilege. Usually contains a value of YES or NO.

Using the above information, we can view the privileges in the MySQL server using the query:

SELECT * FROM information_schema.SCHEMA_PRIVILEGES;

This command will give you an output as shown.

Example 3 – Show running processes

We can use the PROCESSLIST table available in the INFORMATION_SCHEMA table to see the running operations on the server.

Here is an example query to show all running processes:

SELECT * FROM information_schema.PROCESSLIST;

Dumping all the running processes will show an output similar to the one shown:

You can also use the command

SHOW FULL PROCESSLIST, which will show similar information as querying the PROCESSLIST table in the INFORMATION_SCHEMA.

Example 4 – Show Table Indexes Information

Using the STATISTICS table, we can show information about table indexes. Here is an example query:

SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema = 'sakila' LIMIT 5;

Information about all the indexes in the sakila schema as shown below:

Those are some of the examples that you can query from the INFORMATION_SCHEMA database.

Conclusion

The INFORMATION_SCHEMA database is a source of information for all other databases and the MySQL server itself. In addition, it provides you with a plethora of options to manage and tweak for the information to query. If you combine the power of INFORNMATION_SCHEMA and the magic of SQL, you have the most power of any database user.

Thank you for reading & SQL time!