各位大老
各位大老,很急!
我的试图一班都是这样
create view view_name
as
select a.*,b.col,b.col3
from a
left join b on a.col4=b.col4

问题出在,当我向表a中新家一个字段,view就乱码了!又要运行一下这条修改的视图语句!由于视图太多,这样很麻烦,有没有那个大老有好的解决办法呢?
好办法,愿意送上150分,谢谢

[解决办法]
---try
create proc [dbo].[r_view]

AS

BEGIN
declare @sql nvarchar(3000)
declare @viewname varchar(250)
declare @objName varchar(250)
declare @i int

declare #_cursor cursor for
select name from sysobjects where type= 'U ' order by type
open #_cursor

fetch next from #_cursor into @objName
while @@fetch_status=0
begin
set @sql=N 'ALTER INDEX ALL ON '+@objName+
' REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, '+
' STATISTICS_NORECOMPUTE = ON) '
PRINT @sql
EXECUTE sp_executesql @sql,N '@objName varchar(250) ', @objName=@objName

fetch next from #_cursor into @objName
end
close #_cursor
deallocate #_cursor


declare #_cursor cursor for
select name from sysobjects where type= 'TR ' OR type= 'P ' order by type
open #_cursor

fetch next from #_cursor into @objName
while @@fetch_status=0
begin
exec sp_recompile @objName
fetch next from #_cursor into @objName
end
close #_cursor
deallocate #_cursor


set @i=0
declare #_cursor cursor for
select name from sysobjects where type= 'V '

open #_cursor
fetch next from #_cursor into @viewname

while @@fetch_status=0
begin
print '成功刷新视图: '+ @viewname
exec sp_refreshview @viewname
set @i= @i +1
fetch next from #_cursor into @viewname
end

close #_cursor
deallocate #_cursor
print '完成 '
print '共成功刷新 ' + convert(varchar(10),@i) + '个视图 '

END


[解决办法]
一器怎么
[解决办法]
--刷新数据库中所有视图
declare @s nvarchar(4000)
declare tb cursor local for
select 'exec sp_refreshview ' +name
from sysobjects
where xtype= 'v ' and status> =0
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb

[解决办法]
基础表修改后, 视图结构自动更新的最简单的方式就是让相关的视图重新编译.

sp_refreshview '视图名 ' 会让指定的视图在下次运行时重新编译



[解决办法]
你需要重新下view_name

exec sp_refreshview view_name