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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s