Querying metadata

You can query MySQL’s INFORMATION_SCHEMA views to retrieve metadata about objects. These views can be queried directly, like any other table, to return information about tables, columns, and constraints.

Sample queries

List tables and columns in column order

Returns each table and its columns in the order they appear. Includes the owner to avoid cross-schema ambiguity.

SELECT 
  TABLE_SCHEMA AS Owner, 
  TABLE_NAME 
  COLUMN_NAME
FROM 
  INFORMATION_SCHEMA.COLUMNS
ORDER BY 
  TABLE_SCHEMA, 
  TABLE_NAME, 
  ORDINAL_POSITION;

List foreign keys with parent table, delete rule, and columns

Shows each foreign key, its delete rule, the parent (referenced) table, and the foreign-key columns in the correct column order.

SELECT
  R.REFERENCED_TABLE_NAME AS ParentTable,
  K.CONSTRAINT_NAME       AS FkName,
  R.DELETE_RULE           AS DeleteRule,
  K.COLUMN_NAME           AS FkColumn
FROM 
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
JOIN 
  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
  ON K.CONSTRAINT_NAME = R.CONSTRAINT_NAME
  AND K.CONSTRAINT_SCHEMA = R.CONSTRAINT_SCHEMA
WHERE 
  K.POSITION_IN_UNIQUE_CONSTRAINT IS NOT NULL
ORDER BY 
  R.REFERENCED_TABLE_NAME,
  K.CONSTRAINT_NAME;

List tables with foreign keys referencing ACCOUNTS

Returns tables that reference a specific parent table. Add the parent owner if known.

SELECT DISTINCT
-- If the parent table's owner (schema) is known, add this: 
  R.UNIQUE_CONSTRAINT_SCHEMA AS Owner,
  K.TABLE_NAME AS ChildTable
FROM 
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
JOIN 
  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
  ON K.CONSTRAINT_NAME = R.CONSTRAINT_NAME
  AND K.CONSTRAINT_SCHEMA = R.CONSTRAINT_SCHEMA
WHERE 
  R.REFERENCED_TABLE_NAME = 'ACCOUNTS'
-- If the parent table's owner (schema) is known, add this:
-- AND R.UNIQUE_CONSTRAINT_SCHEMA = 'SchemaName'
ORDER BY 
  K.TABLE_NAME;