我们在使用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
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