vendredi 30 janvier 2015

How to write a query which recursively returns all foreign key references to a column?


I need to obtain a list of foreign keys which refer to a given column, as well as all foreign keys which refer to those keys, and so on. Order does not matter. I have



SELECT
s.name,
t.name,
c.name,
fk.name
FROM
sys.foreign_key_columns fkc
INNER JOIN sys.foreign_keys fk
ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c
ON c.object_id = fkc.parent_object_id
AND c.column_id = fkc.parent_column_id
INNER JOIN sys.tables t
ON t.object_id = fkc.parent_object_id
INNER JOIN sys.schemas s
ON s.schema_id = t.schema_id
WHERE
fkc.referenced_object_id = OBJECT_ID(N'dbo.MyTable')
AND fkc.referenced_column_id = (
SELECT TOP 1
column_id
FROM
sys.columns
WHERE
name = 'MyColumn'
AND object_id = OBJECT_ID(N'dbo.MyTable')
)


However, this just returns the foreign keys which immediately reference the column, not the entire tree of foreign keys referencing this column.





Aucun commentaire:

Enregistrer un commentaire