For those of you who followed this thread, did anyone use this method to handle foreign keys with circular references?

I adapted a couple of the posted examples, and against one of my databases which i know has circular references, I'm thinking this is not really going to give results in a hierarchy correctly; it seems that the hierarchy is more of the count of FK levels between the objects, and not necessarily the hiarchy order.

i was fiddling around with this trying to get all objects in a hierarchy, but

It seems to me to be pretty inefficient; 20 plus minutes on a machine with no resources in use at all, along with 60,000 rows;

;WITH ChildrenAndParents

AS

(

SELECT

parent_object_id AS object_id,

convert(varchar(255),OBJECT_NAME(parent_object_id)) AS child,

referenced_object_id AS referenced_major_id,

convert(varchar(255),OBJECT_NAME(referenced_object_id)) AS parent

FROM sys.foreign_keys

)

,GroupMembers (Child, ParentGroup, Level, hierarchypath)

AS

(-- Anchor member definition

SELECTg.child,

g.parent,

0 AS Level,

convert(varchar(max), g.child + '->' + g.parent) AS hierarchypath

FROM ChildrenAndParents AS g

WHERE child not in (select parent from ChildrenAndParents)

UNION ALL

-- Recursive member definition

SELECTg.child,

g.parent,

Level + 1,

hierarchypath + '->' + g.parent -- Add '--&gt...' to end when recursion found

+ Case When gm.hierarchypath like '%->'+g.parent+'->%' Then '--&gt...'

Else''

End

FROM ChildrenAndParents as g

INNER JOIN GroupMembers AS gm

ON gm.parentgroup = g.child

--Exclude if the hierarchypath text contains a recursion

where gm.hierarchypath not like '%-&gt...'

)

select left(hierarchypath, charindex('->', hierarchypath) - 1) as child, parentgroup, level, hierarchypath

from groupmembers

option(maxrecursion 20);