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]
, 1AS [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.
