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

最新推荐文章于 2024-09-05 09:34:12 发布

DigJim

最新推荐文章于 2024-09-05 09:34:12 发布

阅读量2.1k

点赞数

分类专栏： T-SQL技巧文章标签：数据库 object table null function 算法

本文链接：https://blog.csdn.net/digjim/article/details/94052

版权

T-SQL技巧专栏收录该内容

4 篇文章 0 订阅

订阅专栏

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