, a code gallery for creating, saving and sharing all kinds of code snippets.

Table relationship query — SQL

Posted On: Saturday, May 17, 2014 at 1:14 AM

A query that lists all the tables with relationships to other tables, showing what columns are linked.

, AS NameOfForeignKey
, AS FKColumn
, AS ReferencedTable
, AS ReferencedColumn
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.foreign_keys AS fk ON fkc.constraint_object_id = fk.object_id
INNER JOIN sys.tables AS t ON fkc.parent_object_id = t.object_id
INNER JOIN sys.tables AS rt ON fkc.referenced_object_id = rt.object_id
INNER JOIN sys.columns AS pc ON fkc.parent_object_id = pc.object_id
AND fkc.parent_column_id = pc.column_id
INNER JOIN sys.columns AS c ON fkc.referenced_object_id = c.object_id
AND fkc.referenced_column_id = c.column_id

You need to be logged in to comment! x


Joined on Saturday, May 3, 2014
Contributed 29 Snippets

Get Social and Share