The query below returns the foreign key constraints 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,
       fks.constraint_name,
       group_concat(kcu.column_name
            order by position_in_unique_constraint separator ', ') 
             as fk_columns
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 fks.constraint_schema = 'database name'
group by fks.constraint_schema,
         fks.table_name,
         fks.unique_constraint_schema,
         fks.referenced_table_name,
         fks.constraint_name
order by fks.constraint_schema,
         fks.table_name;

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

Columns

• foreign_table - foreign table name with its database (schema) name
• rel - relationship symbol implicating direction
• primary_table - primary (referenced) table name with its database (schema) name
• fk_constraint_name - foreign key constraint name
• fk_columns - foreign key columns separated by ','

Rows

• One row: represents one foreign key. If the foreign key consists of multiple columns (composite key), it is still represented as one row
• Scope of rows: all foreign keys in the database (schema)
• Ordered by: foreign database name and table name

Sample results

You could also get this

Get this interactive HTML data dictionary in minutes with Dataedo.

See live HTML data dictionary sample

Try for free