SqlServer中的对象依赖关系处理

红太狼 2022-08-23 12:49 291阅读 0赞

SqlServer中的对象依赖关系处理

1、sp\_depends  
查找表的依奈项

例1：sp\_depends 'tblItem'

注意：直接使用sp\_depends来查看依赖性，是不可靠的。

我们可能需要进一步用脚本的方式来实现查找，但在以下情况这种方法会失效：

1. 存储过程是加密的。

2. 存储过程的注释代码中包含它所依赖的对像名字。

3. 存储过程的代码过长，它所依赖的对像的名字恰好被分割存储在 syscomments 的两行中。

\--------------------------------------------------------------------------------  
\-- for sql server 2000  
\--------------------------------------------------------------------------------  
declare @procedure\_name nvarchar(255)  
set @procedure\_name = N'CustOrderHist'

select distinct  
procedure\_name = object\_name(c.id)  
,table\_name = t.name  
from dbo.syscomments c  
inner join (select name from dbo.sysobjects where xtype='U') t  
on ( c.text like '% ' + t.name + ' %'  
or c.text like '%.' + t.name + ' %'  
or c.text like '% !\[' + t.name + '!\] %' escape '!'  
or c.text like '%.!\[' + t.name + '!\] %' escape '!'  
)  
where c.id = object\_id(@procedure\_name)

\--------------------------------------------------------------------------------  
\-- for sql server 2005  
\--------------------------------------------------------------------------------  
declare @procedure\_name nvarchar(255)  
set @procedure\_name = N'CustOrderHist'

select distinct  
procedure\_name = object\_name(c.id)  
,table\_name = t.name  
from sys.syscomments c  
inner join sys.tables t  
on ( c.text like '% ' + t.name + ' %'  
or c.text like '%.' + t.name + ' %'  
or c.text like '% !\[' + t.name + '!\] %' escape '!'  
or c.text like '%.!\[' + t.name + '!\] %' escape '!'  
)  
where c.id = object\_id(@procedure\_name)

2、取得当前数据库中对象的依赖关系  
作用：如果对一个数据的依赖关系不清楚，可以用这个函数分析一下。

create function udf\_GenLevelPath()  
returns @v\_Result table (LevelPath int,OName sysname)  
/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/  
/\* 功能描述：按照依赖关系，列出数据库对象 \*/  
/\* 输入参数：无 \*/  
/\* 输出参数：按照依赖关系排列的数据库对象表，无依赖的在前 \*/  
/\* 编写：我 \*/  
/\* 时间：2003-9-9 \*/  
/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/  
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

参考：[http://topic.csdn.net/t/20030911/14/2248894.html][http_topic.csdn.net_t_20030911_14_2248894.html]

[http_topic.csdn.net_t_20030911_14_2248894.html]: http://topic.csdn.net/t/20030911/14/2248894.html