您的位置:澳门新葡8455最新网站 > 数据库管理 > 澳门新葡萄京娱乐场:列出数据库中有数据的表

澳门新葡萄京娱乐场:列出数据库中有数据的表

发布时间:2019-12-09 15:19编辑:数据库管理浏览(58)

    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

        ---游标循环遍历--
        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

    本文由澳门新葡8455最新网站发布于数据库管理,转载请注明出处:澳门新葡萄京娱乐场:列出数据库中有数据的表

    关键词:

上一篇:没有了

下一篇:没有了