T-SQL script to find hierarchy of tables – Row-wise result

,

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.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

1 (1)

Share

Share

Rate

1 (1)