Sql server 2005找出子表樹

同事在准備新老系統的切換，清空一個表的時候往往發現這個表的主鍵被另一個表用做外鍵，而系統裡有太多層次的引用.所以清起來相當麻煩

用下面這個腳本可以做到找出一個特定表的引用樹，比如 table2 有個外鍵引用到了table1　table3有個外鍵飲用到了table2　.......

Code

declare @tbname nvarchar(256);
set @tbname=N'dbo.aspnet_Applications';
with fkids as
(
select
object_id(CONSTRAINT_NAME) as FkId,
object_id(UNIQUE_CONSTRAINT_NAME) AS PkId
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
)
,realations as
(
select p.parent_object_id as pktableId
,f.parent_object_id as fktableid
,i.pkid,i.fkid
from
fkids i inner join sys.objects p on i.pkid=p.[object_id]
inner join sys.objects f on i.fkid=f.[object_id]
)
,cte as
(
select * from realations where pktableid=object_id(@tbname)
union all
select r.* from cte c join realations r on r.pktableid=c.fktableid
)
select
object_name(pktableid) as pktable
,object_name(fktableid) as fktable
,object_name(pkid) as pk
,object_name(fkid) as fk from cte