T-SQL script to find hierarchy of tables – Row-wise result from Blog Posts – SQLServerCentral

As I promised in my previous post T-SQL script to find hierarchy of tables – Columnar result – SQL Server Carpenter^[1], 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 * 
FROM cte_Hierarchy_Of_Tables
ORDER BY Parent, Heirarchy_Sequence ASC

The query will return the result as can be seen in below image.

The post T-SQL script to find hierarchy of tables – Row-wise result^[2] appeared first on SQLServerCentral^[3].

Source: Programming News