在线免费午夜视频,在线a亚洲v天堂网2018影,huangse网站

您當前所在位置：首頁 → 數(shù)據(jù)庫 → MSSQL → 獲得當前數(shù)據(jù)庫對象依賴關系的實用算法

獲得當前數(shù)據(jù)庫對象依賴關系的實用算法 時間：2015/6/28來源：IT貓撲網(wǎng)作者：網(wǎng)管聯(lián)盟我要評論(0)

本文主要介紹了一個獲得當前數(shù)據(jù)庫對象依賴關系的實用算法，具體示例請大家參考下文：

以下為引用的內(nèi)容：
create   function   udf_GenLevelPath()
returns   @v_Result   table   (LevelPath   int,OName   sysname)
/****************************************************************/
/* 功能描述：按照依賴關系，列出數(shù)據(jù)庫對象 */
/* 輸入?yún)?shù)：無 */
/* 輸出參數(shù)：按照依賴關系排列的數(shù)據(jù)庫對象表，無依賴在前 */
/* 編寫： 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
--得到所有對象，不包括系統(tǒng)對象
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)

--循環(huán)處理：由對象而得到其依賴對象
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

關鍵詞標簽：數(shù)據(jù)庫

相關閱讀

文章評論

查看所有0條評論>>