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