Get the Foreign Key Hierarchy

This procedure is just another approach for getting a list of tables defined in a database, ordered by dependency on other tables based on foreign key relationship.
I used it for a deployment process, where the TABLE create scripts must be executed in the correct order,because the foreign key constraints are specified in the create script as well.
You can install it in the master db and run it for any db on the same server



SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


create proc GetTablesOrderedbyRelationship
@database varchar(50)


as
SET NOCOUNT ON


DECLARE
@Level INT,
@MovedToNewLevel INT,
@sql varchar(1024)


CREATE TABLE #Hierarchy
(Level INT,
Name VARCHAR(100),
id numeric)




-- 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


Ref: http://www.sqlservercentral.com