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.

4 thoughts on “T-SQL script to find hierarchy of tables – Row-wise result

  1. I think this statement/relation would be better:
    —————-
    SELECT
    f.name AS ForeignKey,
    OBJECT_NAME(f.parent_object_id) AS TableName,
    COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
    OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
    COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
    —————-

    “Keep it simple!”

    Like

  2. I think this statement would be better:
    —————————-
    SELECT
    f.name AS ForeignKey,
    OBJECT_NAME(f.parent_object_id) AS TableName,
    COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
    OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
    COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
    ——————————

    “Keep it simple !”

    Like

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