Lowell (6/19/2013)
for a single table, i have this saved from Jason's website(the guy who posted before you
--http://jasonbrimhall.info/2011/07/11/foreign-key-hierarchy-update/
--SET XACT_ABORT ON
--BEGIN TRAN
DECLARE@StartParentTableVARCHAR(256)
SELECT @StartParentTable = 'GMACT'
;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)
--ROLLBACK TRAN
--COMMIT TRAN
Thanks for that follow-up Lowell.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events