How to list all relations in a database with parent/child tables and columns involved.

I just finished a meta data query about relations of a database, which might be helpful to others as well.

It simply results in all relations or foreign key constraints of a database with child and parent table, foreign and primary key and the columns involved.
The important thing is, it also covers listing compound foreign (and primary) keys with all columns. In such a case a foreign key will be listed twice or more with ordinal_positions 1,2,3.
The tricky part is, such compound foreign keys don't necessarily refer to a compound primary key, they might also refer to a unique index of the parent table, but this query covers both cases:

[code T-SQL]Select
kcu.TABLE_NAME as ChildTable,
kcu.CONSTRAINT_NAME as ForeignKey,
obj.NAME as ParentTable,
idx.NAME as PrimaryKey_or_UniqueIndex,
kcu.COLUMN_NAME as ChildTableColumn,
col.NAME as ParentTableColumn,
kcu.ORDINAL_POSITION
From INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
Inner Join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on kcu.CONSTRAINT_NAME = c.CONSTRAINT_NAME
Inner Join sys.indexes idx on idx.NAME = c.UNIQUE_CONSTRAINT_NAME
Inner Join sys.objects obj on obj.OBJECT_ID = idx.OBJECT_ID
Inner Join sys.index_columns ic on ic.OBJECT_ID = idx.OBJECT_ID and ic.INDEX_ID = idx.INDEX_ID and kcu.ORDINAL_POSITION = ic.INDEX_COLUMN_ID
Inner Join sys.columns col on col.OBJECT_ID = ic.OBJECT_ID and col.COLUMN_ID = ic.COLUMN_ID
Order By kcu.TABLE_NAME, kcu.CONSTRAINT_NAME, kcu.ORDINAL_POSITION[/code]

Feel free to make use of this.

Edit: This also reveals how you might make a compound foreign key. It's not as simple as specifying several fields of the child and parent table, the first step has to be defining a compound primary key or a compound index with unique constraint, which will be used to make the join.

The syntax for creating such a foreign key does not reveal this:

This depends on a primary key or unique index in the parent table, which contains the two fields, otherwise trying to establish such a foreign key results in an error stating the missing primary or unique index. It's talking of a candidate key, which a unique index is.

Bye, Olaf.