我是用这样的方法来刷新视图的。 如果视图里面用了*,而源表又新增了字段的话,就需要刷新视图了。 USE [ConfigDB] GO /****** Object: StoredProcedure [dbo].[p_UpdateViews] Script Date: 12/29/2007 17:38:35 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GOCREATE Proc [dbo].[p_UpdateViews] @ViewName sysName = NULL /* Author:zsli Date:2007-02-13 Func:手工刷新视图的结构 Para:@ViewName -- 要刷新的视图名,为空刷新当前数据库里的所有视图 */ As
Declare @sql Nvarchar(100),@vName sysname
IF @ViewName is null Begin Declare cur_view Cursor For Select [name] From sys.objects Where type = 'V' Open cur_view Fetch cur_view Into @vName While @@fetch_status = 0 Begin Set @sql = 'sp_refreshview '+@vName Exec(@sql) Print 'View ' + Upper(@vName) + ' refresh succeeds!'+Char(10) Fetch cur_view Into @vName End Close cur_view Deallocate cur_view End Else Begin IF Not Exists(Select [name] From sys.objects Where name = @ViewName and type = 'V') Begin print Upper(@ViewName) + ' Is not a valid view name!' return End
Set @sql = 'sp_refreshview '+@ViewName Exec(@sql) Print 'View ' + Upper(@ViewName) + ' refresh succeeds!' End
应该就好了
-------------------
是不是alter一下也可以呀。还有:是有很多视图呀,还是视图中有很多表呀。
如果视图里面用了*,而源表又新增了字段的话,就需要刷新视图了。
USE [ConfigDB]
GO
/****** Object: StoredProcedure [dbo].[p_UpdateViews] Script Date: 12/29/2007 17:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOCREATE Proc [dbo].[p_UpdateViews]
@ViewName sysName = NULL
/*
Author:zsli
Date:2007-02-13
Func:手工刷新视图的结构
Para:@ViewName -- 要刷新的视图名,为空刷新当前数据库里的所有视图
*/
As
Declare @sql Nvarchar(100),@vName sysname
IF @ViewName is null
Begin
Declare cur_view Cursor For Select [name] From sys.objects Where type = 'V'
Open cur_view
Fetch cur_view Into @vName
While @@fetch_status = 0
Begin
Set @sql = 'sp_refreshview '+@vName
Exec(@sql)
Print 'View ' + Upper(@vName) + ' refresh succeeds!'+Char(10)
Fetch cur_view Into @vName
End
Close cur_view
Deallocate cur_view
End
Else
Begin
IF Not Exists(Select [name] From sys.objects Where name = @ViewName and type = 'V')
Begin
print Upper(@ViewName) + ' Is not a valid view name!'
return
End
Set @sql = 'sp_refreshview '+@ViewName
Exec(@sql)
Print 'View ' + Upper(@ViewName) + ' refresh succeeds!'
End