At the urging of a friend, this script is being updated for those that are dealing with Case Sensitivity. The first few rounds, I neglected Case Sensitivity and never tested for that. It makes sense to have this script updated for that if anybody out there is using it.
The updates are simple enough, it is just frustrating if you run into an error caused by CS and then you waste time troubleshooting it. Believe me, it has happened to me recently – and I don’t much like it.
Without further ado, here is the udpated script:
DECLARE @StartParentTable VARCHAR(256)
SELECT @StartParentTable = 'Calendar'
;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)Still on the todo list is to make this bad boy run faster in the event of circular references. If you find something else with it that you think could use adjusting, let me know.
