This query lists foreign keys constraints with referenced constraints.
Query
select fk_tco.table_schema as foreign_schema,
fk_tco.table_name as foreign_table,
fk_tco.constraint_name as foreign_constraint,
'>-' as rel,
pk_tco.table_schema as referenced_schema,
pk_tco.table_name as referenced_table,
pk_tco.constraint_name as referenced_constraint
from information_schema.referential_constraints rco
join information_schema.table_constraints fk_tco
on fk_tco.constraint_name = rco.constraint_name
and fk_tco.constraint_schema = rco.constraint_schema
join information_schema.table_constraints pk_tco
on pk_tco.constraint_name = rco.unique_constraint_name
and pk_tco.constraint_schema = rco.unique_constraint_schema
order by fk_tco.table_schema,
fk_tco.table_name;
Columns
- foreign_schema- foreign schema name
- foreign_table - foreign table name
- foreign_constraint - foreign key constraint name
- rel - relationship symbol implicating direction
- referenced_schema - rerefenced schema name
- referenced_table - rerefenced table name
- referenced_constraint - foreign key constraint name
Rows
- One row represents one foreign key constraint
- Scope of rows: all foregin keys in a database
- Ordered by schema name and name of foreign table
Sample results
You could also get this
Get this interactive HTML data dictionary in minutes with Dataedo.