WITH Fkeys AS (
SELECT DISTINCT
OnTable = OnTable.name
,AgainstTable = AgainstTable.name
FROM
SYSFOREIGNKEYS fk
INNER JOIN SYSOBJECTS onTable
ON fk.fkeyid = onTable.id
INNER JOIN SYSOBJECTS againstTable
ON fk.rkeyid = againstTable.id
WHERE 1=1
AND AgainstTable.TYPE = 'U'
AND OnTable.TYPE = 'U'
-- ignore self joins; they cause an infinite recursion
AND OnTable.Name <> AgainstTable.Name
)
,MyData AS (
SELECT
OnTable = o.name
,AgainstTable = FKeys.againstTable
FROM
SYS.OBJECTS O
LEFT JOIN FKeys
ON o.name = FKeys.onTable
WHERE 1=1
AND o.type = 'U'
AND o.name NOT LIKE 'sys%'
)
,MyRecursion AS (
-- base case
SELECT
TableName = OnTable
,Lvl = 1
FROM
MyData
WHERE 1=1
AND AgainstTable IS NULL
-- recursive case
UNION ALL SELECT
TableName = OnTable
,Lvl = r.Lvl + 1
FROM
MyData d
INNER JOIN MyRecursion r
ON d.AgainstTable = r.TableName
)
SELECT
ROW_NUMBER() OVER (ORDER BY MAX(lvl) ASC) AS SNO
,Lvl = MAX(Lvl)
,TableName
FROM
MyRecursion
GROUP BY
TableName
ORDER BY
1 DESC
,2 DESC
