MetaData_TableDependenceOrder

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, rnamefrom #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
")