MetaData_TableDependenceOrder

,

MetaData_TableDependenceOrder Lists User tables in foreign key dependence order.
I use this to determine data load order without having to disable foreign key constraints
accepts a database name (required) and an optional comma delimited list of tables to filter the dependency list with. If tablelist is not passed, it returns all tables with a load ranking from parent to child.
examples:
MetaData_TableDependenceOrder @SourceDb = 'mydb'
MetaData_TableDependenceOrder @SourceDb = 'mydb','t1, t2, t3, t4'



if exists (select * from dbo.sysobjects where id = object_id(N'MetaData_TableDependenceOrder') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure MetaData_TableDependenceOrder
GO

create proc MetaData_TableDependenceOrder
@SourceDb varchar(200),
@tablelist varchar(8000) = ''
as

execute(" use " + @SourceDb + " " +"

declare @tableList  varchar(8000)
set @tableList = '" + @tableList + "'
set nocount on
set quoted_identifier off
declare @Precedence_Level  int 
create table #tableorder  (tablename varchar(100),Precedence_Level int, LoadOrder int identity(1,1) )
create table #tableorderwork (tablename varchar(100),Precedence_Level int, LoadOrder int identity(1,1) )
create table #refwork  (fname varchar(100),rname varchar(100))

insert into #refwork
select distinct object_name(fkeyid) fname ,object_name(rkeyid) rname 
from sysreferences  

insert into #tableorderwork
select distinct table_name , 0
from information_schema.tables 
left join ( select distinct fname, rname	from #refwork ) as r 
on table_name = fname  
where table_type = 'base table' and rname is null 

set @Precedence_Level = 0
while @@rowcount > 0
begin 
	set @Precedence_Level = @Precedence_Level + 1 
	insert into #tableorderwork  
	select  distinct fname,@Precedence_Level  
   from ( 
   	select t4.fname,t4.tablecount a_count,t5.tablecount r_count  
	   from ( 
      	select fname,count(*) tablecount 
         from ( 
         	select distinct fname, rname 
         	from #refwork  
         	where fname in (
               select distinct fname
            	from #refwork  
            	where rname in (select tablename from #tableorderwork) 
            	and fname not in  (select tablename from #tableorderwork) 
         	) 
      	) as temp1 
       	where fname <> rname 
       	group by fname 
   	) as t4 inner join ( 
      	select distinct fname ,count(*) tablecount 
         from ( 
         	select distinct fname ,tablename 
            from ( 
         	select distinct fname, rname 
         	from #refwork  
         	   where fname in  ( 
            	   select distinct fname
   	            from #refwork  
               	where rname in (select tablename from #tableorderwork) 
               	and fname not in  (select tablename from #tableorderwork) 
            	) 
            ) as t1 left join ( select tablename from #tableorderwork ) as t2 on t1.rname  = t2.tablename 
      	) as t3 
   	   where tablename is not null  
      	group by fname 
	   ) as t5 on t4.fname = t5.fname 
	) as t6 
	where a_count = r_count 
   
end 




if datalength(@tablelist) = 0
begin
   insert into #tableorder(tablename,Precedence_Level)
   select tablename,Precedence_Level
   from #tableorderwork 
   order by Precedence_Level asc ,tablename 
end 
else
begin
   declare @tableliststart int, @NextValueStart int, @Value nvarchar(4000)
   --declare @ValueTable table(Value nvarchar(4000))
   create table #ValueTable(Value nvarchar(4000))
   if @tablelist is not null and @tablelist <> ''
   begin
      set @NextValueStart = charindex(',', @tablelist, 0)
      if @NextValueStart = 0
      begin
         insert #ValueTable ( Value ) values ( @tablelist )
      end
      else 
      begin
         set @tableliststart = 0
         while @tableliststart <= len(@tablelist) + 1
         begin
            insert #ValueTable ( Value ) values ( substring(@tablelist, @tableliststart, @NextValueStart - @tableliststart) )
            set @tableliststart = @NextValueStart + 1
            set @NextValueStart = charindex(',', @tablelist, @tableliststart + 1)
            if @NextValueStart = 0 set @NextValueStart = len(@tablelist) + 1
         end
      end
   end

   insert into #tableorder(tablename,Precedence_Level)
   select t.tablename,Precedence_Level
   from #tableorderwork t
   inner join #ValueTable tablelist  on t.tablename = tablelist.value
   order by Precedence_Level asc ,t.tablename 
end

select tablename,Precedence_Level,LoadOrder from #tableorder
order by Precedence_Level,LoadOrder,tablename
")

Rate

Share

Share

Rate