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

2015-06-28 00:00作者：网管联盟

本文主要介绍了一个获得当前数据库对象依赖关系的实用算法，具体示例请大家参考下文：

以下为引用的内容：
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