‘   — 关键此处填写需要刷新视图的数据库名称金沙网址:

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

相关文章