试试这个,将所有的视图重新生成一次,测试过没问题后,去掉事务处理,以便真正更改视图.
begin tran
declare @viewname varchar(250),@id int,@text varchar(8000)
declare #aa cursor for select id,name
from sysobjects
where objectproperty(id,'IsView')=1 and uid=1 and left(name,3)<>'sys'
open #aa
fetch next from #aa into @id,@viewname
while @@fetch_status=0
begin
select @text=''
select @text=@text+char(13)+text
from syscomments
where id=@id
exec('drop view '+@viewname)
-- print @viewname
exec(@text)
-- print @text
fetch next from #aa into @id,@viewname
end
close #aa
deallocate #aa
begin tran UpdateViews
declare @viewname varchar(250),@id int,@text varchar(8000)
declare @text2 varchar(8000)
set @text2=''
select id,name into temptb
from sysobjects
where objectproperty(id,'IsView')=1 and uid=1 and left(name,3)<>'sys' order by name
--如果不用这个临时表,在下面的游标会出错
--也就是会重复对一个视图进行反复的删除和新建
declare #aa cursor for select * from temptb
open #aa
declare @count int
select @count=0
fetch next from #aa into @id,@viewname
while @@fetch_status=0
begin
select @count=@count+1
select @text=''
select @text=@text+char(13)+text
from syscomments
where id=@id and colid=1
select @text2=''
select @text2=@text2+char(13)+text
from syscomments
where id=@id and colid=2