mysql数据库依赖关系_如何获得当前数据库对象依赖关系_MySQL

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