How to obtain the source of the object dependency example of the current database
CodeFor more information, see the following:
Create function udf_GenLevelPath ()
Returns @ v_Result table (LevelPath int, OName sysname)
/*************************************** *************************/
/* Function description: lists dependencies.DatabaseObject*/
/* Input parameter: None */
/* Output parameters: database object tables arranged by dependency. no dependency exists */
/* Write: anna */
/* Time: 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)
-- Dependency level. the smaller the value, the stronger the dependency.
Declare @ vi_LevelPath int
Set @ vi_LevelPath = 1
-- Get all objects, excluding system objects
Insert into @ vt_ObjDepPath (LevelPath, OName)
Select @ vi_LevelPath, o. name
From sysobjects o
Where xtype not in ('s ', 'x ')
-- Get the name of the dependent object
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)
-- Cyclic processing: Get the dependent object from the object
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
-- Modify the maximum object level of the non-dependent object
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
-- Call method
Select * from dbo. udf_GenLevelPath ()
Go
|
