INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

T-SQL Hints and Tips

How to list all relations in a database with parent/child tables and columns involved. by Olaf Doschke
Posted: 10 Feb 15 (Edited 11 Feb 15)

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 

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:

CODE

ALTER TABLE ChildTable
   ADD CONSTRAINT FK_Child_Parent
   FOREIGN KEY (field1, field2) 
   REFERENCES ParentTable (field1, field2) 

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.

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close