use master
go
drop procedure sp_spacehistory
go
create procedure sp_spacehistory
as
declare @pages int
declare @objname varchar(30)
declare @id int
set nocount on
create table #spt_space
(id int, 
rows int null, 
reserved int null, 
data int null, 
indexp int null, 
unused int null)
declare spusage cursor for select name, id from sysobjects where type = 'u' order by 1
open spusage
fetch next from spusage into @objname, @id
while (@@fetch_status <> -1)
begin
  if (@@fetch_status <> -2)
  begin
	insert #spt_space (id, rows) select id, rows 
			from sysindexes 
				where indid < 2
					and id = @id
	/*
	**  Now calculate the summary data.
	**  reserved: sum(reserved) where indid in (0, 1, 255)
	*/
	update #spt_space set reserved = (
		select sum(reserved)
			from sysindexes
				where indid in (0, 1, 255)
					and id = @id)
			where id = @id

	/*
	**   data: sum(dpages) where indid < 2
	**	+ sum(used) where indid = 255 (text)
	*/
	select @pages = sum(dpages)
			from sysindexes
				where indid < 2
					and id = @id
	select @pages = @pages + isnull(sum(used), 0)
		from sysindexes
			where indid =  255
				and id = @id
	update #spt_space
		set data = @pages where id = @id

	/* index: sum(used) where indid in (0, 1, 255) - data */
	update #spt_space
		set indexp = (select sum(used)
				from sysindexes
					where indid in (0, 1, 255)
						and id = @id) - data
                    where id = @id

	/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
	update #spt_space
		set unused = reserved
				- (select sum(used)
					from sysindexes
						where indid in (0, 1, 255)
							and id = @id)
                    where id = @id
  end
  fetch next from spusage into @objname, @id
end
--print out result
select dbname=db_name(),
name = substring(object_name(id), 1, 20), 
'record date'=convert(datetime,convert(char(11),getdate(),13)),
rows, 
'reserved KB'=(reserved*d.low/1024),
'data KB'=(data*d.low/1024),
'index_size KB'=(indexp*d.low/1024),
'unused KB'=(unused*d.low/1024)
from #spt_space, master.dbo.spt_values d
where d.number=1 and d.type = 'E'
deallocate spusage