/****** Object: StoredProcedure SYS_REFRESHVIEW Script Date: 12/17/2012 10:32:46 ******/
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'SYS_REFRESHVIEW') AND
type IN ( N'P', N'PC' ) )
DROP PROCEDURE SYS_REFRESHVIEW
GO
/*
刷新数据库视图.sql
*/
CREATE PROCEDURE SYS_REFRESHVIEW
AS
BEGIN
DECLARE @vName SYSNAME
DECLARE refresh_cursor CURSOR
FOR
SELECT Name
FROM sysobjects
WHERE xtype = 'V'
ORDER BY crdate FOR READ ONLY
OPEN refresh_cursor
FETCH NEXT FROM refresh_cursor INTO @vName
WHILE @@FETCH_STATUS = 0
BEGIN
--刷新指定视图的元数据。由于视图所依赖的基础对象的更改,视图的持久元数据会过期。
BEGIN TRY
EXEC sp_refreshview @vName
--PRINT '视图' + @vName + '已更新'
FETCH NEXT FROM refresh_cursor INTO @vName
END TRY
BEGIN CATCH
PRINT '视图' + @vName + '异常:' + ERROR_MESSAGE()
ROLLBACK
FETCH NEXT FROM refresh_cursor INTO @vName
END CATCH
END
CLOSE refresh_cursor
DEALLOCATE refresh_cursor
END
GO
MSSQL 刷新全数据库视图 脚本
最新推荐文章于 2024-08-20 10:54:35 发布