INSERT Object Ids SELECT OBJECT ID(' Sales . Customer ')

SET NOCOUNT ON

GO

DECLARE @object_ids TABLE(o_id INT)

-- Insert table(s) here

INSERT @object_ids SELECT OBJECT_ID('[Sales].[Customer]')

DECLARE @list TABLE (obj_id INT NOT NULL)

INSERT @list (obj_id) SELECT o_id FROM @object_ids;

WITH distinct_fks AS

(

SELECT

DISTINCT

parent_object_id,

referenced_object_id

FROM

sys.foreign_keys

WHERE

parent_object_id <> referenced_object_id -- Avoid self references

),

rec_cte AS

(

SELECT

fk.parent_object_id,

fk.referenced_object_id,

0 AS lvl

FROM

distinct_fks fk INNER JOIN

@list l

ON

fk.referenced_object_id = l.obj_id

UNION ALL

SELECT

fks.parent_object_id,

fks.referenced_object_id,

rec_cte.lvl + 1 AS lvl

FROM

distinct_fks fks INNER JOIN

rec_cte

ON

fks.parent_object_id = rec_cte.referenced_object_id

WHERE

fks.referenced_object_id <> rec_cte.parent_object_id -- To avoid infinite loops when tables refer to themselves

)

SELECT

DISTINCT

OBJECT_NAME(rec_cte.referenced_object_id) AS referenced_object,

rec_cte.lvl

FROM

rec_cte INNER JOIN

(

SELECT

referenced_object_id,

MAX(lvl) AS max_lvl

FROM

rec_cte

GROUP BY

referenced_object_id

) AS max_rec_cte

ON

rec_cte.referenced_object_id = max_rec_cte.referenced_object_id AND

rec_cte.lvl = max_rec_cte.max_lvl

ORDER BY

lvl DESC

OPTION (MAXRECURSION 32767)