获得当前数据库中对象的依赖关系的算法

create function udf_GenLevelPath_Table()
?returns @v_Result table (LevelPath int,OName sysname,type varchar(8))
/****************************************************************/
/*?功能描述:按照依赖关系,列出数据库对象????????????????????????????????????????????? ?*/
/*?输入参数:无?????????????????????????????????????????????????????????????????????????????????????????????????? */
/*?输出参数:按照依赖关系排列的数据库对象表,无依赖的在前?????????????? */
/*?编??????? 写:我????????????????????????????????????????????????????????????????????????????????????????????????? ?*/
/*?时间:2003-9-9??????????????????????????????????????????????????????????????????????????????????????????????? */
/****************************************************************/

as
begin
?declare @vt_ObjDepPath table (LevelPath int,OName sysname null, type Varchar(8))
?declare @vt_Temp1 table (OName sysname null)
?declare @vt_Temp2 table (OName sysname null)
?declare @vi_LevelPath int

?set @vi_LevelPath = 1
?insert into @vt_ObjDepPath(LevelPath,OName,type)
??select @vi_LevelPath,o.name,o.type
???from sysobjects o
???where xtype not in ('S','X')
?
?insert into @vt_Temp1(OName)
??select distinct object_name(sysdepends.depid)
???from sysdepends,@vt_ObjDepPath p
???where sysdepends.id <> sysdepends.depid
????and p.OName = object_name(sysdepends.id)
?
?while (select count(*) from @vt_Temp1) > 0
?begin
??set @vi_LevelPath = @vi_LevelPath + 1
?
??update @vt_ObjDepPath
???set LevelPath = @vi_LevelPath
???where OName in (select OName from @vt_Temp1)
????and LevelPath = @vi_LevelPath - 1
?
??delete from @vt_Temp2
?
??insert into @vt_Temp2
???select * from @vt_Temp1
?
??delete from @vt_Temp1
?
??insert into @vt_Temp1(OName)
???select distinct object_name(sysdepends.depid)
????from sysdepends,@vt_Temp2 t2
????where t2.OName = object_name(sysdepends.id)
?????and sysdepends.id <> sysdepends.depid

?end

?select @vi_LevelPath = max(LevelPath) from @vt_ObjDepPath

?update @vt_ObjDepPath
??set LevelPath = @vi_LevelPath + 1
??where OName not in (select distinct object_name(sysdepends.id) from sysdepends)
???and LevelPath = 1
?
?insert into @v_Result
??select * from @vt_ObjDepPath order by LevelPath desc
?return
end
go

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值