Last month I published an update to my Foreign Key Hierarchy script. Today, I am providing a new update for that script. A friend (Rémi Grégoire) helped out with some mods to this script. The change for this month is nothing too intrusive. The script is now updated for those databases that are Case Sensitive.
[codesyntax lang=”tsql”]
DECLARE @StartParentTable VARCHAR(256)
SELECT @StartParentTable = 'my starting table'
;With Hierarchy (ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol) AS (
SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
,0 AS FKLevel
,CAST('.'+CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX))+'.' AS VARCHAR(MAX))
FROM sys.foreign_key_columns sfkc
INNER Join sys.foreign_keys sfk
ON sfkc.constraint_object_id = sfk.OBJECT_ID
WHERE OBJECT_NAME(sfk.referenced_object_id) = @StartParentTable
UNION All
SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
,FKLevel + 1
,STUFF(('.' + F.SortCol + CAST(CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX)) + '.' AS VARCHAR(MAX))
),1,1,'')
FROM sys.foreign_keys sfk
INNER Join Hierarchy F
ON OBJECT_NAME(sfk.referenced_object_id) = F.ChildTable
And F.SortCol NOT LIKE '%'+CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX))+'%'
INNER Join sys.foreign_key_columns sfkc
ON sfkc.constraint_object_id = sfk.OBJECT_ID
WHERE OBJECT_NAME(sfk.referenced_object_id) <> @StartParentTable
And sfk.referenced_object_id <> sfk.parent_object_id
), Ancestry (ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol) AS (
SELECT DISTINCT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
,-1 AS FKLevel
,CAST('.'+CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX))+'.' AS VARCHAR(MAX))
FROM Hierarchy F
INNER Join sys.foreign_keys sfk
ON F.ChildTable = OBJECT_NAME(sfk.parent_object_id)
And F.ParentTable <> OBJECT_NAME(sfk.referenced_object_id)
INNER Join sys.foreign_key_columns sfkc
ON sfkc.constraint_object_id = sfk.OBJECT_ID
UNION All
SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
,F.FKLevel -1
,STUFF(('.' + SortCol + CAST(CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX)) + '.' AS VARCHAR(MAX))
),1,1,'')
FROM Ancestry F
INNER Join sys.foreign_keys sfk
ON F.ParentTable = OBJECT_NAME(sfk.parent_object_id)
INNER Join sys.foreign_key_columns sfkc
ON sfkc.constraint_object_id = sfk.OBJECT_ID
WHERE F.ParentTable not in (SELECT ParentTable FROM Hierarchy)
And sfk.referenced_object_id <> sfk.parent_object_id
And F.ChildTable not IN (SELECT ChildTable FROM Hierarchy)
)
SELECT ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol
FROM Hierarchy
UNION All
SELECT ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol
FROM Ancestry
ORDER BY SortCol ASC
OPTION (maxrecursion 500)[/codesyntax]
This update should make it more usable for any that may be using it or is interested in using it. Thanks for Rémi for taking the time to propose this update.
