(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
       --========================================================
       *****************************************************