As I promised in my previous post T-SQL script to find hierarchy of tables – Columnar result – SQL Server Carpenter, here is another version of the query to get the hierarchy of tables in the form of rows.
; WITH cte_Hierarchy_Of_Tables AS ( SELECT A.[name] AS [Parent] , A.[object_id] AS [Parent_Object_ID] , A.[name] AS [Child] , A.[object_id] AS [Child_Object_ID] , 1 AS [Heirarchy_Sequence] FROM sys.tables A LEFT JOIN sys.foreign_keys B ON B.parent_object_id = A.object_id WHERE B.object_id IS NULL AND A.name LIKE 'Parent%' -- If you want the hierarchy of specific table(s), filter them here. UNION ALL SELECT CTE.[Parent] , FK.parent_object_id , TBL.[name] AS [Child] , TBL.[object_id] AS [Child_Object_ID] , CTE.[Heirarchy_Sequence] + 1 AS [Heirarchy_Sequence] FROM sys.foreign_keys FK INNER JOIN sys.tables TBL ON TBL.object_id = FK.parent_object_id INNER JOIN cte_Hierarchy_Of_Tables CTE ON FK.[referenced_object_id] = CTE.[Parent_Object_ID] ) SELECT [Parent] , [Child] , [Heirarchy_Sequence] FROM cte_Hierarchy_Of_Tables ORDER BY Parent, Heirarchy_Sequence ASC
The query will return the result as can be seen in below image.