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