MSSQL 刷新全数据库视图脚本

最新推荐文章于 2024-09-07 16:30:53 发布

勿忘心安Billy

最新推荐文章于 2024-09-07 16:30:53 发布

阅读量1.5k

点赞数

分类专栏： MSSQL笔记

本文链接：https://blog.youkuaiyun.com/qq415734794/article/details/8306460

版权

MSSQL笔记专栏收录该内容

3 篇文章

订阅专栏

本文提供了一个SQL脚本用于批量刷新数据库中的所有视图，确保视图的元数据与基础对象保持同步。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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