T-SQL script to find hierarchy of tables – Columnar result

One of my LinkedIn connection reached out to me for help, to find the hierarchy of all the tables in a database. He was in dire need of some help, since there are around 250 tables in his database, and he need to write delete script to delete data requested by his team.

As we all know, things become very complex when table is referenced to another table via foreign key and this could go to N level of foreign key references.

For example, there is table1, which is referenced by table2, referenced by table3, referenced by table4, and so on so forth.

In this query, I’ve considered up-to 10 level. Reason for considering only 10 was simple. Because I’ve never personally seen any foreign key reference going beyond 5 level. Anyways, you can add up-to N number levels even beyond 10, by simple adding it in the FOR clause of PIVOT section in the query.

I wrote two different variants of the query – one will return the data in the columnar format as can be seen in the image below.

I’ll write a separate post for another version of the query which will return the data row-wise as can be seen in the following image.

Here is the query that’ll return columnar result as we already discussed.

; 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]
)
, cte_Staging
AS 
(
SELECT Parent
, STRING_AGG(CONVERT(NVARCHAR(max), Child), ',') AS Child
, Heirarchy_Sequence
FROM cte_Hierarchy_Of_Tables
GROUP BY Parent, Heirarchy_Sequence
)
SELECT Parent, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]
FROM cte_Staging
PIVOT
(
MAX(Child)
FOR [Heirarchy_Sequence] IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) 
) AS PVT