SQL Server 计算表增长情况

  if  object_id('table_reserved_new') is not null 
  begin
     delete from table_reserved_new;
  end   
  else
  begin
       create table table_reserved_new(name varchar(100),row int,reserved int,data int,index_size int,unused int) 
  end
   
  if  object_id('table_reserved_old') is  null 
  begin
       create table table_reserved_old(name varchar(100),row int,reserved int,data int,index_size int,unused int) 
  end
      
  if  object_id('table_increase') is  null 
  begin
       create table table_increase(name varchar(100),row int,reserved int,data int,index_size int,unused int, insert_time datetime) 
  end
  
  
create table #Data(name varchar(100),row varchar(100),reserved varchar(100),data varchar(100),index_size varchar(100),unused varchar(100)) 
declare @name varchar(100) 
declare cur cursor  for 
    select name from sysobjects where xtype='U' order by name 
open cur 
fetch next from cur into @name 
while @@fetch_status=0 
begin 
    insert into #Data 
    exec sp_spaceused   @name 
    print @name 
 
    fetch next from cur into @name 
end 
close cur 
deallocate cur 
 
--create table #DataNew(name varchar(100),row int,reserved int,data int,index_size int,unused int) 
-- 获取当前各表的存储信息 
insert into table_reserved_new
select name,convert(int,row) as row,convert(int,replace(reserved,'KB','')) as reserved,convert(int,replace(data,'KB','')) as data, 
convert(int,replace(index_size,'KB','')) as index_size,convert(int,replace(unused,'KB','')) as unused from #Data  

 ---begin 开始对比增长量
--select   *  from #DataNew order by data desc, row desc
--select * from table_reserved
declare @table_name varchar(100) 
declare @row int
declare @reserved int
declare @data int
declare @index_size int
declare @unused int

  declare cur1 cursor  for 
    select name, [row], reserved, data, index_size, unused from table_reserved_new 
open cur1 
fetch next from cur1 into @table_name , @row, @reserved, @data, @index_size, @unused
while @@fetch_status=0 
begin 
      insert into table_increase
       select @table_name as table_name,
         @row - [row] as [rows],
          @reserved - reserved as reserved,
          @data - [data] as [data],
          @index_size - index_size as index_size,
          @unused - unused as unused,
          getdate()
      from  table_reserved_old  where [name] = @table_name 
    fetch next from cur1 into @table_name , @row, @reserved, @data, @index_size, @unused
end 
close cur1 
deallocate cur1 
---begin 结束对比增长量
 
 delete from table_reserved_old;
  insert into table_reserved_old select *  from  table_reserved_new;
      
drop table #Data