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_newselect 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_reserveddeclare @table_name varchar(100) declare @row intdeclare @reserved intdeclare @data intdeclare @index_size intdeclare @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, @unusedwhile @@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, @unusedend close cur1 deallocate cur1 ---begin 结束对比增长量 delete from table_reserved_old; insert into table_reserved_old select * from table_reserved_new; drop table #Data
