微软版:
微软对此的说明“用于更新指定的未绑定到架构的视图的元数据。由于视图所依赖的基础对象的更改,视图的持久元数据会过期。”,前半句没看懂。
USE AdventureWorks;
GO
SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''
FROM sys.objects AS so
INNER JOIN sys.sql_expression_dependencies AS sed
ON so.object_id = sed.referencing_id
WHERE so.type = 'V' AND sed.referenced_id = OBJECT_ID('Person.Contact');
'Person.Contact'是我们所修改的基本表,刷新所有依赖此表的视图。
得到一个类似
EXEC sp_refreshview 'view1'
EXEC sp_refreshview 'view2'
EXEC sp_refreshview 'view3'
EXEC sp_refreshview 'view4'
这样的列表,复制后手动执行。
网络版:
CREATE PROCEDURE RefreshAllView AS
DECLARE MyCursor CURSOR
FOR select Name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsView') = 1 and (not name in ('sysconstraints','syssegments'))
DECLARE @name varchar(40)
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
begin
exec sp_refreshview @name
end
FETCH NEXT FROM MyCursor INTO @name
END
CLOSE MyCursor
DEALLOCATE MyCursor
“网络版”来自:http://www.cnblogs.com/yashen/archive/2004/12/23/81000.html
生成存储过程,自动刷新全部视图。