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