As I promised in my previous post T-SQL script to find hierarchy of tables – Columnar result, 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.
SQL Server Carpenter 
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!”
LikeLike
How would you print the heirarchy with this query? I would love to learn a new way!
The purpose of the post was to find the hierarchy of the tables.
LikeLike
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 !”
LikeLike
How would you print the heirarchy with this query?
The purpose of the post was to find the hierarchy of the tables.
LikeLike
Does it handle tables having self-join? I am getting a maximum recursion error when using this query. Don’t know what is breaking it. But good effort.
LikeLike
It should Shiv!
You can try addition following line at the end
OPTION (MAXRECURSION 0)
LikeLike
Hi nice reading yyour blog
LikeLike