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] , 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] ) , 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, , , , , , , , , ,  FROM cte_Staging PIVOT ( MAX(Child) FOR [Heirarchy_Sequence] IN ( , , , , , , , , , ) ) AS PVT