Query to find out the size of tables and indexes in any Sybase tempdb
by : Mich Talebzadeh (Copyright)

Note: This has not been tested by me.Please try it yourself first, modify and post them here if required.

To find out the size of tables and indexes in any tempdb, the owners and the processes creating them, you can use the following stored procedure. However, you need to execute it in the tempdb database it is intended for:

use sybsystemprocs
go
create procedure sp__tempdb3
as
create table #trouble3 (name varchar(30), indid int, size int)
insert #trouble3
select a.name, b.indid, data_pgs(b.id, doampg)*1024/512
from tempdb3..sysobjects a,
tempdb3..sysindexes b
where a.id=b.id
and (b.indid <2) and a.type='U'
union
select a.name, b.indid, isnull(sum(data_pgs(b.id, ioampg))*1024/512,0)
from tempdb3..sysobjects a,
tempdb3..sysindexes b
where a.id=b.id and b.indid >1 and (a.type='U')
group by a.name, b.indid
select distinct substring(suser_name(p.suid),1,20) "User",
p.spid "Process",
a.name "Table",
a.indid,
size "Size in KB"
from #trouble3 a,
master..sysprocesses p
where a.name not like '#trouble3%'
and suser_name(p.suid) <> 'sa'
and
(
convert(int,substring(a.name,17,4)) = p.spid
OR convert(int,substring(a.name,18,5)) = p.spid
)
order by suser_name(p.suid),a.size desc
truncate table #trouble3
drop table #trouble3
go
grant all on sp__tempdb3 to public
go
exit


For example to find out all temporary work tables created in tempdb3, do the following:
use tempdb3
go
sp__tempdb3
go
User Process Table indid Size in KB
-------------------- ------- ------------------------------ ----------- -----------
jobserver 1033 #BH11________00010330010239290 0 18
jobserver 938 #BH14________00009380010240200 0 14
jobserver 1143 #BH9_________00011430010238698 0 10
jobserver 239 #BH20________00002390010242200 0 8
jobserver 1246 #BH4_________00012460010237517 0 6


Note that indid column has the usual values in sysindexes. For example “0” for the table itself, “1” for clustered index etc. In addition, you can somehow decipher the #table name and find out the name of the Sybase login creating it. The stored procedure code above should give you enough information about how to find this out.