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

--批量查询表空间
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

希望能帮助到大家，