Q9.25: sp_spaceused_table
Brief
In environment where there are a lot of temporary tables #x being
created, how do you tell who is using how much space ?
This is a problem because the object names are munged in the tempdb.
I solved this problem by creating another procedure from sp_spaceused
which used the object_id as its parameter instead of the name.
The ksh script which runs through the object ids and the
procedure sp_spaceused_table (modified sp_spaceused ) are
attached.
sp_spaceused_table
<![CDATA[
use sybsystemprocs
go
create procedure sp_spaceused_table
@object_id int
as
declare @type smallint, /* the object type */
@msg varchar(250), /* message output */
@dbname varchar(30), /* database name */
@tabname varchar(30), /* table name */
@length int,
@objname varchar(92), /* the object we want size on */
@list_indices int /* don't sum all indices, list each */
select @objname = NULL, @list_indices = 0
if @@trancount = 0
begin
set chained off
end
set transaction isolation level 1
if not exists (select * from sysobjects where id = @object_id and type = "U")
begin
print "The table does not exists in the current database."
return (1)
end
set nocount on
/*
** We want a particular object.
*/
begin
select name = o.name,
iname = i.name,
low = d.low,
rowtotal = rowcnt(i.doampg),
reserved = convert(numeric(20,9),
(reserved_pgs(i.id, i.doampg) +
reserved_pgs(i.id, i.ioampg))),
data = convert(numeric(20,9),data_pgs(i.id, i.doampg)),
index_size = convert(numeric(20,9),
data_pgs(i.id, i.ioampg)),
unused = convert(numeric(20,9),
((reserved_pgs(i.id, i.doampg) +
reserved_pgs(i.id, i.ioampg)) -
(data_pgs(i.id, i.doampg) +
data_pgs(i.id, i.ioampg))))
into #pagecounts
from sysobjects o, sysindexes i, master.dbo.spt_values d
where i.id = @object_id
and o.id = @object_id
and d.number = 1
and d.type = "E"
if (@list_indices = 1)
begin
select @length = max(datalength(iname))
from #pagecounts
if (@length > 20)
select index_name = iname,
size = convert(char(10), convert(varchar(11),
convert(numeric(11,0),
index_size / 1024 *
low)) + " " + "KB"),
reserved = convert(char(10),
convert(varchar(11),
convert(numeric(11,0),
reserved / 1024 *
low)) + " " + "KB"),
unused = convert(char(10), convert(varchar(11),
convert(numeric(11,0), unused / 1024 *
low)) + " " + "KB")
from #pagecounts
else
select index_name = convert(char(20), iname),
size = convert(char(10), convert(varchar(11),
convert(numeric(11,0),
index_size / 1024 *
low)) + " " + "KB"),
reserved = convert(char(10),
convert(varchar(11),
convert(numeric(11,0),
reserved / 1024 *
low)) + " " + "KB"),
unused = convert(char(10), convert(varchar(11),
convert(numeric(11,0), unused / 1024 *
low)) + " " + "KB")
from #pagecounts
end
select @length = max(datalength(name))
from #pagecounts
if (@length > 20)
select distinct name,
rowtotal = convert(char(11), sum(rowtotal)),
reserved = convert(char(15), convert(varchar(11),
convert(numeric(11,0), sum(reserved) *
(low / 1024))) + " " + "KB"),
data = convert(char(15), convert(varchar(11),
convert(numeric(11,0), sum(data) * (low / 1024)))
+ " " + "KB"),
index_size = convert(char(15), convert(varchar(11),
convert(numeric(11,0), sum(index_size) *
(low / 1024))) + " " + "KB"),
unused = convert(char(15), convert(varchar(11),
convert(numeric(11,0), sum(unused) *
(low / 1024))) + " " + "KB")
from #pagecounts
else
select distinct name = convert(char(20), name),
rowtotal = convert(char(11), sum(rowtotal)),
reserved = convert(char(15), convert(varchar(11),
convert(numeric(11,0), sum(reserved) *
(low / 1024))) + " " + "KB"),
data = convert(char(15), convert(varchar(11),
convert(numeric(11,0), sum(data) * (low / 1024)))
+ " " + "KB"),
index_size = convert(char(15), convert(varchar(11),
convert(numeric(11,0), sum(index_size) *
(low / 1024))) + " " + "KB"),
unused = convert(char(15), convert(varchar(11),
convert(numeric(11,0), sum(unused) *
(low / 1024))) + " " + "KB")
from #pagecounts
end
return (0)
go
]]>
ksh script
<![CDATA[
#!/bin/ksh
if [ $# -ne 1 ]
then
echo "usage: $0 <pisql|disql|dbcisql...>"
exit 1
fi
ISQL=$1
TMP=/tmp/$$
$ISQL <<! | egrep "[0-9][0-9][0-9]" > $TMP
use tempdb
go
select id from sysobjects where type = "U"
go
!
for i in `cat $TMP`
do
echo use tempdb
echo go
echo sp_spaceused_table $i
echo go
done | $ISQL -e
rm $TMP
]]>