MSSQL 刷新全数据库视图脚本

刷新SQL视图存储过程

最新推荐文章于 2025-11-29 18:01:51 发布

最新推荐文章于 2025-11-29 18:01:51 发布 · 166 阅读

文章标签：

#数据库

本文介绍了一个用于刷新SQL Server数据库中所有视图元数据的存储过程。该过程使用游标遍历所有视图，并调用sp_refreshview来更新它们。当视图依赖的对象发生变化时，此操作可以确保视图的数据保持最新。

/****** 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