修改基础表后，刷新关联视图的两种方法

微软版：

微软对此的说明“用于更新指定的未绑定到架构的视图的元数据。由于视图所依赖的基础对象的更改，视图的持久元数据会过期。”，前半句没看懂。

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

生成存储过程，自动刷新全部视图。