if exists (select * from sysobjects where id =object_id('dbo.sp_allspace') and sysstat & 0xf = 4)
drop procedure dbo.sp_allspace
GO
create procedure sp_allspace
-- IMPORTANT! usage info. should be updated before running me.
-- Empty tables are ommitted from the results.
as
declare @objname varchar(92) -- The object we want size on.
declare @id int -- The object id of @objname.
declare @type smallint -- The object type.
declare @pages int -- Working variable for size calc.
declare @dbname varchar(30)
declare @dbsize dec(15,0)
declare @trows dec(15,0)
declare @tall dec(15,0)
declare @tdata dec(15,0)
declare @tidx dec(15,0)
declare @tuu dec(15,0)
declare @srows dec(15,0)
declare @sall dec(15,0)
declare @sdata dec(15,0)
declare @sidx dec(15,0)
declare @suu dec(15,0)
declare @irows dec(15,0)
declare @iall dec(15,0)
declare @idata dec(15,0)
declare @iidx dec(15,0)
declare @iuu dec(15,0)
declare @iname varchar(20)
declare @msg varchar(80)
declare AllTables insensitive cursor
for
select name from sysobjects order by name
open AllTables
/*
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
create table #spt_space
(
name varchar(92),
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
set nocount on
select getdate()
fetch next from AllTables into @objname
/*
** Insert the row for the table
*/
insert into #spt_space (name,rows,reserved,data,indexp,unused)
values ("zzUser Table Totals",0,0,0,0,0)
insert into #spt_space (name,rows,reserved,data,indexp,unused)
values ("zzSystem Table Totals",0,0,0,0,0)
while @@fetch_status = 0
begin
/*
** Find the object.
*/
select @id = null
select @id = id, @type = sysstat & 0xf
from sysobjects
where id = object_id(@objname)
/*
** See if it's a space object.
** types are:
** 1 - system table
** 2 - view
** 3 - user table
** 4 - sproc
** 6 - default
** 7 - rule
** 8 - trigger
** 9 - primary key
** 10 - check constraint
** 11 - foreign key
** 12 - replication filter stored proc
*/
if @type in (1,3)
begin
/*
** Insert the row for the table
*/
insert into #spt_space (name)
values (@objname)
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
/* insert into #spt_space (reserved) */
select @iall = sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
update #spt_space
set reserved = @iall
where name = @objname
/* Update Totals */
if @type = 3
update #spt_space
set reserved = reserved + @iall
where name = "zzUser Table Totals"
else
update #spt_space
set reserved = reserved + @iall
where name = "zzSystem Table Totals"
/*
** 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 name = @objname
/* Update Totals */
if @type = 3
update #spt_space
set data = data + @pages
where name = "zzUser Table Totals"
else
update #spt_space
set data = data + @pages
where name = "zzSystem Table Totals"
/* index: sum(used) where indid in (0, 1, 255) - data */
select @iidx = sum(used)
from sysindexes
where indid in (0, 1, 255) and id = @id
update #spt_space
set indexp = @iidx - data
where name = @objname
/* Update Totals */
if @type = 3
update #spt_space
set indexp = indexp + (@iidx - data)
where name = "zzUser Table Totals"
else
update #spt_space
set indexp = indexp + (@iidx - data)
where name = "zzSystem Table Totals"
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved - @iidx
where name = @objname
/* Update Totals */
if @type = 3
update #spt_space
set unused = unused + (reserved - @iidx)
where name = "zzUser Table Totals"
else
update #spt_space
set unused = unused + (reserved - @iidx)
where name = "zzSystem Table Totals"
select @irows = i.rows
from sysindexes i
where i.indid < 2 and i.id = @id
update #spt_space
set rows = @irows
where name = @objname
/* Update totals */
if @type = 3
update #spt_space
set rows = rows + @irows
where name = "zzUser Table Totals"
else
update #spt_space
set rows = rows + @irows
where name = "zzSystem Table Totals"
end
fetch next from AllTables into @objname
end
/* end while loop here and print out totals */
select "Table" = substring(#spt_space.name, 1, 20),
"Rows" = str(rows,12),
"Reserved KB" = (str(reserved * d.low / 1024.,15)),
"Data Space KB" = (str(data * d.low / 1024.,15)),
"Index Space KB" = (str(indexp * d.low / 1024.,15)),
"Unused Space KB" = (str(unused * d.low / 1024.,15))
from #spt_space, master.dbo.spt_values d
where (d.number = 1 and d.type = 'E')
and rows <> 0 -- do not show empty tables
order by #spt_space.name
close AllTables
deallocate AllTables
return (0)
GO
?bill's home