MSSQL 刷新全数据库视图 脚本

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