利用游标批量清空数据同时批量查询数据空间和条数

--批量查询表空间
drop table #temp
create table #temp (
name varchar(80),
rows  int,
reserved char(100),
date char(100),
index_size char(100),
unused char(100))

declare @tablename varchar(80)
declare @sql varchar(500)
declare cur cursor for
select name from sysobjects where xtype='u'

open cur
fetch next from cur into  @tablename
while @@FETCH_STATUS=0
 begin 
   set @sql='insert into #temp exec sp_spaceused '+@tablename
   exec (@sql)
   fetch next from cur into @tablename
 end
 
 close cur
 deallocate cur
 
 ------------------------------------------------------
 select * from #temp 
 
 
 ----------------------------------------------------------------
 --批量清空表数据
 declare @TABLENAME VARchar(100)
 DECLARE @SQL VARCHAR(500)
 declare cur cursor for 
 select NAME from sysobjects where xtype='U'
 
 OPEN CUR
 FETCH NEXT FROM CUR INTO @TABLENAME
   WHILE @@FETCH_STATUS =0
     BEGIN 
        SET @sql='TRUNCATE TABLE '+@tablename 
        EXEC (@SQL)
        FETCH NEXT FROM CUR INTO @TABLENAME
     END
 CLOSE CUR
 DEALLOCATE CUR
 
 
 

希望能帮助到大家，