网络编程
位置：首页>> 网络编程>> 数据库>> Sql server 2005 找出子表树

Sql server 2005 找出子表树

作者：Tianjon　来源：博客园　发布时间：2008-11-24 15:23:00　

标签：

同事在准备新老系统的切换，清空一个表的时候往往发现这个表的主键被另一个表用做外键，而系统里有太多层次的引用.所以清起来相当麻烦

用下面这个脚本可以做到找出一个特定表的引用树，比如 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 

0
投稿

猜你喜欢

手机版 网络编程 asp之家 www.aspxhome.com