sql server - List SQL Table Relationships with composite keys -

what sql list table relationships, composite keys/foreign fields correctly lined together? here's mean:

i use sql below table relationships:

select     c.constraint_name,     cu.table_schema referencingschema,     cu.table_name referencingtable,     cu.column_name referencingcolumn,     ku.table_schema referencedschema,     ku.table_name referencedtable,     ku.column_name referencedcolumn     information_schema.referential_constraints c     inner join information_schema.constraint_column_usage cu on         cu.constraint_name = c.constraint_name     inner join information_schema.key_column_usage ku on         ku.constraint_name = c.unique_constraint_name 

which works great single-column primary keys, when composite keys involved things fall apart. e.g. fk below there 2 columns participate in relationship, 4 misaligned results returned (because of cartesian products):

+-------------------------+------------------+-------------------+-------------------+------------------+ |constraint_name          |referencingtable  | referencingcolumn | referencedtable   | referencedcolumn | +-------------------------+------------------+-------------------+-------------------+------------------+ |fk_account_chainid_chain | account          | chainid           | chain             | chainid          | |fk_account_chainid_chain | account          | chainid           | chain             | ntentid          | |fk_account_chainid_chain | account          | ntentid           | chain             | chainid          | |fk_account_chainid_chain | account          | ntentid           | chain             | ntentid          | +-------------------------+------------------+-------------------+-------------------+------------------+ 

i want result be:

+-------------------------+------------------+-------------------+-------------------+------------------+ |constraint_name          |referencingtable  | referencingcolumn | referencedtable   | referencedcolumn | +-------------------------+------------------+-------------------+-------------------+------------------+ |fk_account_chainid_chain | account          | chainid           | chain             | chainid          | |fk_account_chainid_chain | account          | ntentid           | chain             | ntentid          | +-------------------------+------------------+-------------------+-------------------+------------------+ 

now use natural-like join on referencing- , referenced-columns line fields - adding and cu.column_name = ku.column_name join, work if names same , in many instances not (not of doing).

---

i looked in information_schema.key_column_usage , has ordinal_position field ordering primary keys, haven't seen similar column in information_schema.constraint_column_usage , i'm not sure else look.

so if/where/how can line referencing , referenced columns in composite table relationship?

---

applies sql server 2008 r2 , above.

the following should give need, used sys schema views, instead of information_schema:

select     quotename(fk.name) constraint_name,     quotename(s.name) + '.' + (t.name) referencingtable,     quotename(c.name) referencingcolumn,     quotename(s2.name) + '.' + quotename(t2.name) referencedtable,     quotename(c2.name) referencedcolumn sys.foreign_keys fk     join sys.foreign_key_columns fkc on fkc.constraint_object_id = fk.object_id     join sys.tables t on t.object_id = fk.parent_object_id     join sys.schemas s on s.schema_id = t.schema_id     join sys.tables t2 on t2.object_id = fk.referenced_object_id     join sys.schemas s2 on s2.schema_id = t2.schema_id     join sys.columns c on c.column_id = fkc.parent_column_id , c.object_id = fk.parent_object_id     join sys.columns c2 on c2.column_id = fkc.referenced_column_id , c2.object_id = fk.referenced_object_id order fk.name, fkc.constraint_column_id; 

the above return information fks.

you can filter composite adding clause:

select ... ...     (select count(1) sys.foreign_key_columns constraint_object_id = fk.object_id) > 1 order fk.name, fkc.constraint_column_id;