快速刷新视图

2019-11-09 09:30 来源:未知

DECLARE @tableNames varchar(500)
SET @tableNames='xxx,DB2, '   -- 关键此处填写需要刷新视图的数据库名称

列表出有数据的表:

DECLARE @i_start int
SET @i_start=1;

select name from sysobjects o where type='u' and exists ( select * from sysindexes where id=o.id and indid<2 and rows>0 )

DECLARE @i_end int
SET @i_end = CHARINDEX(',', @tableNames, @i_start);

 

DECLARE @tableName varchar(30)

OR

declare @s nvarchar(1000) -- 注意此处改为nvarchar(1000)

select o.name from sysobjects o,sysindexes i where o.type='u' and i.id=o.id and i.indid<2 and i.rows>0

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 @tablename varchar(100),@ii varchar(10),@temp nvarchar(1000),@temp2 nvarchar(1000) declare @i int,@count int,@datecount int select @count=count(*) from sysobjects where xtype='u' set @i=1 while(@i<=@count) begin set @ii=@i set @temp='select top 1 @name=name from(select top '+@ii+' name from sysobjects where xtype=''u'' order by name)as a order by name desc' exec sp_executesql @temp,N'@name nvarchar(50) output',@tablename output set @temp2='select @date=count(*) from '+@tablename+'' exec sp_executesql @temp2,N'@date int output',@datecount output if(@datecount>0) begin exec('select * from '+@tablename+'') print @tablename end set @i=@i+1 end

TAG标签:
版权声明:本文由澳门新浦金网站发布于数据库,转载请注明出处:快速刷新视图