declare @tablenames varchar(500)
set @tablenames=’xxx,db2, ‘ — 关键此处填写需要刷新视图的数据库名称
declare @i_start int
set @i_start=1;
declare @i_end int
set @i_end = charindex(‘,’, @tablenames, @i_start);
declare @tablename varchar(30)
declare @s nvarchar(1000) — 注意此处改为nvarchar(1000)
while @i_end>0
begin
set @tablename= ltrim(rtrim(substring(@tablenames, @i_start, @i_end-@i_start)))
–select @tablename
if exists(select * from master..sysdatabases where name=@tablename)
begin
print ‘更新 数据库[‘+ @tablename+’]所有视图’
if exists (select * from tempdb.dbo.sysobjects where id = object_id(n’tempdb..#temptable’) and type=’u’)
begin
drop table #temptable
end
create table #temptable (_sql_ nvarchar(1000))
set @s = ‘use ‘+@tablename+’ select ”use ‘+@tablename+ ‘ execute sp_refreshview ” + name from sysobjects where [xtype]=”v”’
insert into #temptable(_sql_)
exec sp_executesql @s
—游标循环遍历–
declare @temp nvarchar(1000)
–声明游标
declare order_cursor cursor
for(select * from #temptable)
–打开游标–
open order_cursor
–开始循环游标变量–
fetch next from order_cursor into @temp
while @@fetch_status = 0 –返回被 fetch语句执行的最后游标的状态–
begin
–print @temp
exec (@temp) –ok
–exec sp_executesql @temp –ok
fetch next from order_cursor into @temp –转到下一个游标，没有会死循环
end
close order_cursor –关闭游标
deallocate order_cursor –释放游标
–用完之后要把临时表清空
drop table #temptable–需要注意的是，这种方法不能嵌套。
end
set @i_start = @i_end + 1;
set @i_end = charindex(‘,’, @tablenames, @i_start);
end