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:
[codesyntax lang=”tsql”]
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
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) |
[/codesyntax]
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.