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