Get the Foreign Key Hierarchy 2

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

create proc GetTablesOrderedbyRelationship
@database varchar(50)

as
SET NOCOUNT ON

DECLARE
@LevelINT,
@MovedToNewLevelINT,
@sql varchar(1024)

CREATE TABLE #Hierarchy
(LevelINT,
NameVARCHAR(100),
idnumeric)


-- Populate the table
set @sql="INSERT INTO #Hierarchy select 0 AS 'Level', name,id"
set @sql=@sql + " FROM " + @database + ".dbo.sysobjects where xtype='U' and"
set @sql=@sql + " id not in (select rkeyid from " + @database + ".dbo.sysforeignkeys) and "
set @sql=@sql + " id not in (select fkeyid from " + @database + ".dbo.sysforeignkeys)"
exec( @sql)

set @sql="INSERT INTO #Hierarchy select 1 AS 'Level', name,id"
set @sql=@sql + " FROM " + @database + ".dbo.sysobjects where xtype='U' and"
set @sql=@sql + " id not in (select id from #Hierarchy) and"
set @sql=@sql + " id in (select rkeyid from " + @database + ".dbo.sysforeignkeys) and "
set @sql=@sql + " id not in (select fkeyid from " + @database + ".dbo.sysforeignkeys)"
exec( @sql)

set @sql="INSERT INTO #Hierarchy select 2 AS 'Level', name,id"
set @sql=@sql + " FROM " + @database + ".dbo.sysobjects where xtype='U' and"
set @sql=@sql + " id not in (select id from #Hierarchy) and"
set @sql=@sql + " id in (select fkeyid from " + @database + ".dbo.sysforeignkeys)"
exec( @sql)

--print(@sql)
--return

-- Set the variables
set @Level=2
set @MovedtoNewLevel=1

WHILE @MovedtoNewLevel <> 0
BEGIN
set @sql="update #Hierarchy set Level=Level+1 where Level=" + CAST(@Level as varchar) + " and"
set @sql=@sql + " id in (select fkeyid from " + @database + ".dbo.sysforeignkeys where fkeyid<>rkeyid and"
set @sql=@sql + " rkeyid in (select id from #Hierarchy where level=" + CAST(@Level as varchar) + " ))"
exec(@sql)
SET @MovedtoNewLevel = @@Rowcount
SELECT @Level = @Level + 1
END

select * from #Hierarchy order by Level
DROP TABLE #Hierarchy


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO