Adsense

Tuesday, April 8, 2014

Get a cascading list of all tables that have a foreign key to a table or any of its dependencies


declare @t table (
foreign_key_name sysname not null,
foreign_table sysname not null,
parent_table sysname not null,
checked bit default 0)

declare @table_to_check as sysname

set @table_to_check = 'table_name'

insert into @t (foreign_key_name, foreign_table, parent_table, checked)
select f.name as foreign_key_name,
c.name as foreign_table,
p.name as parent_table,
case when p.name = c.name then 1 else 0 end
from  sysobjects f
inner join sysobjects c on f.parent_obj = c.id
inner join sysreferences r on f.id = r.constid
inner join sysobjects p on r.rkeyid = p.id
where f.type = 'F'
and p.name = @table_to_check

while exists (select 1 from @t where checked = 0)
begin

      set @table_to_check = (select top 1 foreign_table from @t where checked = 0)

      insert into @t (foreign_key_name, foreign_table, parent_table, checked)
      select distinct f.name as foreign_key_name,
      c.name as foreign_table,
      p.name as parent_table,
      case when p.name = c.name then 1 when t.parent_table is null then 0 else 1 end as checked
      from  sysobjects f
      inner join sysobjects c on f.parent_obj = c.id
      inner join sysreferences r on f.id = r.constid
      inner join sysobjects p on r.rkeyid = p.id
      left outer join @t t on t.parent_table = c.name
      where f.type = 'F'
      and p.name = @table_to_check

      update @t set checked = 1 where foreign_table = @table_to_check
end

select *
from @t

order by foreign_table