List SQL table hierarchically based on Foreign Key and Primary Key Relationship

The below query help us to list the table hierarchically based on Foreign Key and Primary key relationship:


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