SQL SERVER 中对当前库中的所有视图进行刷新的存储过程

最新推荐文章于 2023-11-09 17:01:54 发布

PDZSPDZS

最新推荐文章于 2023-11-09 17:01:54 发布

阅读量838

点赞数

分类专栏： SQL2008 文章标签： sql server 存储 object sqlserver 脚本

SQL2008 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

在sqlserver应用中，有时修改了某些字段或者增加了字段需要对对应的视图进行刷新。如果视图很多，会很麻烦，所以就有了以下脚本的诞生。

 --对当前库中的所有视力进行刷新的存储过程

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


CREATE PROCEDURE SP_Usually AS

DECLARE @ObjectName varchar(500)
DECLARE @ExeSQL varchar(500)

DECLARE Object_Cursor CURSOR FOR
select name from sysobjects where xtype=''v'' and left(name,3)<>''syn''
and left(name,3)<>''sys'' and name<>''v_repl_articles''

OPEN Object_Cursor

FETCH NEXT FROM  Object_Cursor INTO @ObjectName
WHILE(@@FETCH_STATUS=0)

BEGIN

select @ExeSQL =''exec sp_refreshview ''+ @ObjectName
exec (@ExeSQL)

FETCH NEXT FROM  Object_Cursor INTO @ObjectName
END

CLOSE Object_Cursor
DEALLOCATE Object_Cursor


DECLARE Object_Cursor CURSOR FOR
select name from sysobjects where xtype=''p'' and left(name,3)<>''syn''
and left(name,3)<>''sys''

OPEN Object_Cursor

FETCH NEXT FROM  Object_Cursor INTO @ObjectName
WHILE(@@FETCH_STATUS=0)

BEGIN

select @ExeSQL =''exec sp_recompile ''+ @ObjectName
exec (@ExeSQL)

FETCH NEXT FROM  Object_Cursor INTO @ObjectName
END

CLOSE Object_Cursor
DEALLOCATE Object_Cursor

 
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO