(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) https://esdbp.createaforum.com --------------------------------------------------------- ***************************************************** (DIR) Return to: Scripts ***************************************************** #Post#: 45-------------------------------------------------- Table Hirerarchy By: srinivasma_exceldbp Date: April 10, 2014, 9:49 pm --------------------------------------------------------- --** Table Hierarchy with Hierarchy Levels . SET NOCOUNT ON DECLARE @intCounter @intRowCount --======================================================== IF OBJECT_ID('tempdb..#Hierarchy') IS NOT NULL DROP TABLE CREATE TABLE #Hierarchy ( Hierarchy Child Parent ) --======================================================== -- Set the variables SELECT @intCounter SELECT @intRowCount = 1 --======================================================== -- Populate the table INSERT INTO #Hierarchy ( Hierarchy,Child,Parent) SELECT DISTINCT 1 AS 'Hierarchy', S1.name AS 'Child', SO.Name AS 'Parent' FROM dbo.sysforeignkeys FK INNER JOIN dbo.sysobjects SO ON FK.rkeyID = SO.id INNER JOIN dbo.sysobjects S1 ON FK.fkeyID = S1.id --======================================================== WHILE @intRowCount <> 0 BEGIN UPDATE P SET Hierarchy FROM #Hierarchy P WHERE P.Hierarchy AND EXISTS SELECT TOP 1 1 FROM #Hierarchy C WHERE C.Hierarchy AND P.Parent ) SET SELECT END --======================================================== -- Add the tables that have no Foriegn Key relationships... INSERT INTO #Hierarchy ( Hierarchy,Child,Parent) SELECT -1, [name], ' - ' FROM dbo.sysobjects so WHERE NOT EXISTS ( SELECT TOP 1 1 FROM #Hierarchy P WHERE P.Parent = so.[name] ) AND NOT EXISTS SELECT TOP 1 1 FROM #Hierarchy C WHERE C.Child = so.[name] ) AND xtype = 'U' --======================================================== -- Add the tables that are Parents only INSERT INTO #Hierarchy ( Hierarchy,Child,Parent) SELECT DISTINCT 0, Parent, ' - ' From #Hierarchy P WHERE NOT EXISTS ( SELECT Child FROM #Hierarchy C WHERE C.Child = P.Parent ) AND P.Hierarchy <> -1 --======================================================== -- Add 1 to adjust the hierarchies to start at 0 UPDATE #Hierarchy SET Hierarchy = Hierarchy + 1 --======================================================== -- Display the results SELECT DISTINCT Hierarchy, Child, Parent FROM #Hierarchy ORDER BY Hierarchy, Child, Parent -- Clean up --DROP TABLE #Hierarchy --======================================================== *****************************************************