Create PROCEDURE [dbo].[sp_RefreshAllView]

AS

--刷新所有视图

DECLARE @ViewName VARCHAR(MAX);

DECLARE @i INT;

SET @i = 0;

DECLARE #_cursor CURSOR

FOR

SELECT name

FROM sysobjects

WHERE type = 'V';

OPEN #_cursor;

FETCH NEXT FROM #_cursor INTO @ViewName;

WHILE @@fetch_status = 0

BEGIN

begin try

EXEC sp_refreshview @ViewName;

PRINT '成功刷新视图: ' + @ViewName;

SET @i = @i + 1;

end try

begin catch

print '##刷新视图失败：'+@ViewName

print ' 失败原因：'+ERROR_MESSAGE()

rollback tran

end catch

FETCH NEXT FROM #_cursor INTO @ViewName;

END;

CLOSE #_cursor;

DEALLOCATE #_cursor;

PRINT '刷新视图完成';

PRINT '共成功刷新' + CONVERT(VARCHAR(10), @i) + '个视图';