Re: How know many to many relationship from information schema

[Peter Brawley]

This query retrieves foreign keys in schema 'DB' ...

SELECT  
  c.table_schema as 'Parent Schema', 
  u.referenced_table_name as 'Parent Table', 
  u.referenced_column_name as 'Parent Column', 
  u.table_schema as 'Child Schema', 
  u.table_name as 'Child Table', 
  u.column_name as 'Child Column' 
FROM information_schema.table_constraints AS c 
JOIN information_schema.key_column_usage AS u USING(constraint_schema,constraint_name) 
WHERE c.constraint_type = 'FOREIGN KEY' AND u.referenced_table_schema = 'DB';

Two tables have an M:M relationship mediated by foreign keys if they show up in the above query as 'Parent Tables' for a common 'Child Table'.