查找该视图/表被哪些视图引用的存储过程

最新推荐文章于 2022-10-17 14:32:35 发布

转载最新推荐文章于 2022-10-17 14:32:35 发布 · 767 阅读

本文提供两个存储过程，用于查询特定视图被哪些其他视图或存储过程引用。通过遍历系统目录并搜索视图定义中的引用名称来实现。

查找该视图被哪些视图引用的存储过程

alter procedure GetviewYY
@viewNameYY nvarchar(128)

AS
declare @ViewText nvarchar(4000)
declare @viewName nvarchar(128)

declare viewCursor cursor for
Select b.text ,a.name
from sysobjects a
inner join syscomments b on a.id=b.id
where a.xtype='V'

open viewCursor

FETCH NEXT FROM viewCursor
INTO @ViewText,@viewName

WHILE @@FETCH_STATUS = 0
begin
if @viewName<>@viewNameYY and charindex(@viewNameYY,@ViewText )>0
begin
print @viewName
end

FETCH NEXT FROM viewCursor
INTO @ViewText,@viewName
end

CLOSE viewCursor
DEALLOCATE viewCursor

-----

--存储过程

create procedure GetviewPP
@viewNamePP nvarchar(128)
AS
declare @ViewText nvarchar(4000)
declare @viewName nvarchar(128)
declare viewCursor cursor for
Select b.text ,a.name
from sysobjects a
inner join syscomments b on a.id=b.id
where a.xtype='P'
open viewCursor
FETCH NEXT FROM viewCursor
INTO @ViewText,@viewName
WHILE @@FETCH_STATUS = 0
begin
if @viewName<>@viewNamePP and charindex(@viewNamePP,@ViewText )>0
begin
print @viewName
end
FETCH NEXT FROM viewCursor
INTO @ViewText,@viewName
end
CLOSE viewCursor
DEALLOCATE viewCursor