MySQL INFORMATION_SCHEMA Tables

MySQL provides a powerful set of system tables known as the INFORMATION_SCHEMA tables that contain metadata about the database system, including the databases, tables, columns, indexes, privileges, and more.

These tables are a valuable resource for database administrators and developers to query and analyze information about the MySQL server and its objects.

INFORMATION_SCHEMA

The INFORMATION_SCHEMA database is a virtual database that MySQL uses to store metadata about the server and its databases. It provides access to the read-only information about database objects and server statistics. The tables within the INFORMATION_SCHEMA database are organized hierarchically making it easy to navigate and query different aspects of the MySQL server.

Accessing INFORMATION_SCHEMA

Accessing the wealth of information stored in the INFORMATION_SCHEMA tables is as simple as executing SQL queries against them similar to querying any other database tables.

To access the information stored in the INFORMATION_SCHEMA tables we can execute SQL queries against them just like any other database tables.

The syntax for the querying INFORMATION_SCHEMA tables is straightforward:

SELECT column1, column2, …

FROM INFORMATION_SCHEMA.table_name

WHERE condition;

• SELECT: Specifies the columns to the retrieved from the table.
• FROM: Indicates the table from which to retrieve data.
• WHERE: Optionally filters the rows based on the specified conditions.

Examples of MySQL INFORMATION_SCHEMA Tables

1. Retrieve a List of all Databases in the MySQL Server

SELECT SCHEMA_NAME AS Database_Name
FROM INFORMATION_SCHEMA.SCHEMATA;

Output:

Explanation: This query fetches the names of all databases stored in the MySQL server from the SCHEMATA table within the INFORMATION_SCHEMA.

2. Get Information about Columns in a Specific Table

SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'employees';

Output:

Explanation: This query retrieves information about the columns in the employees table including the column name, data type, and maximum character length for the character data types from the COLUMNS table within the INFORMATION_SCHEMA.

Example 3: Check foreign key constraints on a specific table:

SELECT CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'orders' AND CONSTRAINT_NAME LIKE 'fk_%';

Output:

Empty set (0.01 sec)

Explanation: This query attempts to retrieve information about foreign key constraints on the orders table. If no foreign key constraints exist the result set will be empty.

Conclusion

The INFORMATION_SCHEMA tables in MySQL provide a convenient way to access metadata about the database system. By querying these tables we can retrieve valuable information about databases, tables, columns, indexes, privileges, and more. Utilizing INFORMATION_SCHEMA tables can help the database administrators and developers better understand and manage their MySQL server.

In conclusion, the prowess of the INFORMATION_SCHEMA tables in MySQL extends beyond mere data retrieval. They pave the way for enhanced insights, streamlined operations, and informed decision-making thereby enriching the database management experience.