eSnippets.net, 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.

SELECT t.name AS FKTableName
, fk.name AS NameOfForeignKey
, pc.name AS FKColumn
, rt.name AS ReferencedTable
, c.name 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

rpjs

Joined on Saturday, May 3, 2014
Contributed 29 Snippets

Get Social and Share

Tags