Technical Article

Get the Foreign Key Hierarchy 2

,

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

Rate

4.5 (2)

Share

Share

Rate

4.5 (2)