DECLARE @viewName NVARCHAR(500)
-- 创建游标来遍历所有视图
DECLARE viewCursor CURSOR FOR
SELECT name
FROM sys.views -- 获取当前数据库中的所有视图名称
ORDER BY name
-- 打开游标
OPEN viewCursor
-- 获取第一条视图名称
FETCH NEXT FROM viewCursor INTO @viewName
-- 循环遍历每个视图并刷新它
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
-- 动态刷新视图
EXEC sp_refreshview @viewName
PRINT 'Successfully refreshed view: ' + @viewName
END TRY
BEGIN CATCH
-- 捕获并跳过错误,处理事务
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION
END
PRINT 'Failed to refresh view: ' + @viewName + ' - ' + ERROR_MESSAGE()
END CATCH
-- 获取下一条视图名称
FETCH NEXT FROM viewCursor INTO @viewName
END
-- 关闭游标
CLOSE viewCursor
-- 释放游标资源
DEALLOCATE viewCurso