SQL Server修改表结构后批量更新所有视图的存储过程

我们在使用SqlServer时经常遇到这种情况，当修改某个表的结构后，相关的视图就不对了而导致程序错误，因此就有个下面这个存储过程。

 

 

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

每次修改了表结构之后，运行一下就OK了！