create function udf_GenLevelPath() returns @v_Result table (LevelPath int,OName sysname) /****************************************************************/ /* 功能描述:按照依赖关系,列出数据库对象 */ /* 输入参数:无 */ /* 输出参数:按照依赖关系排列的数据库对象表,无依赖在前 */ /* 编写: anna*/ /* 时间:2007-12-12 */ /****************************************************************/ as begin declare @vt_ObjDepPath table (LevelPath int,OName sysname null) 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) select @vi_LevelPath,o.name 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
--调用方法 select * from dbo.udf_GenLevelPath() go
|