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
