The query below returns the foreign key constraint columns defined in the user databases (schemas).

Query

select concat(fks.constraint_schema, '.', fks.table_name) as foreign_table,
       '->' as rel,
       concat(fks.unique_constraint_schema, '.', fks.referenced_table_name)
              as primary_table,
       kcu.ordinal_position as no,
       kcu.column_name as fk_column_name,
       '=' as 'join',
       kcu.referenced_column_name as pk_column_name,
       fks.constraint_name as fk_constraint_name
from information_schema.referential_constraints fks
join information_schema.key_column_usage kcu
     on fks.constraint_schema = kcu.table_schema
     and fks.table_name = kcu.table_name
     and fks.constraint_name = kcu.constraint_name
where kcu.table_schema not in('information_schema','sys',
                              'mysql', 'performance_schema')
--    and fks.constraint_schema = 'database name'
order by fks.constraint_schema,
         fks.table_name,
         kcu.ordinal_position;

Note: if you need the information for a specific database (schema), then uncomment the table_schema condition and provide your database name.

Columns

• foreign_table - foreign table name with database (schema) name
• rel - relationship symbol implicating direction
• primary_table - primary (referenced) table name with database (schema) name
• no - id of the column in the key. Single column keys always have 1, composite keys have 1, 2, ... n for each column of the key
• fk_column_name - foreign table column
• join - "=" symbol indicating the joining operation for a pair of columns
• pk_column_name - primary (referenced) table column
• fk_constraint_name - foreign key constraint name

Rows

• One row: represents one foreign key column. If the foreign key is composed of several columns (composite key), each column appears separately
• Scope of rows: all foreign keys in a database (schema) and its columns
• Ordered by: foreign table database (schema) name, table name and column ordinal position in the key

Sample results