SqlServer中的对象依赖关系处理

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!