1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
| WITH H ([level], [object], object_parent)
AS
(
SELECT
0,
OP.name AS object,
O.name AS object_parent
FROM sys.foreign_keys AS FL
INNER JOIN sysobjects AS O
ON O.id = FL.referenced_object_id
INNER JOIN sysobjects AS OP
ON FL.parent_object_id = OP.id
WHERE O.name = '<maTable>'
UNION ALL
SELECT
H.level + 1,
OP.name,
H.object
FROM sys.foreign_keys AS FL
INNER JOIN sysobjects AS O
ON O.id = FL.referenced_object_id
INNER JOIN sysobjects AS OP
ON FL.parent_object_id = OP.id
INNER JOIN H
ON O.name = H.[object]
)
SELECT *
FROM H
ORDER BY [level] DESC; |
Partager