Create Function UDF_GENLEVEVELPATH_TABLE ()? Returns @V_Result Table (LevelPath Int, Oname Sysname, Type Varchar (8)) / ********************************** ***************************************** * • Function Description: According to the dependency, column Database object ??????????????????????????????????????? * // *? Enter parameters: no ????????????????????????????????????????????? ??????????????????????????????????????????? ?????? * // *? Output parameters: According to the database object table arranged in the dependency, there is no dependence in front ?????????????? * // *? ????? Writing: I ??????????????????????????????????? ??????????????????????????????????????????? ?????? * // *? Time: 2003-9-9 ??????????????????????????????????????? ??????????????????????????????????????????? ?????????????? * // ****************************************** ************************************************* / asbegin? 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, oname, 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? ReturnendGo
