Sysmaster query for determining additional extents availble per table/index
I'd guess someone might find it useful... Works fine on 11.50 but I
haven't tried it on another version... Enjoy...
set isolation to dirty read; select
dbsname as dbname,
tabname as dbobject,
trunc((pg_frcnt/8),0) additional_extents,
count(*)
num_of_extents,
sum( pe_size ) total_size,
ti_nptotal pages_allocated,
ti_npused pages_used,
ti_npdata data_pages,
ti_nrows
data_rows,
ti_rowsize as row_size,
ti_fextsiz first_extent_size,
ti_nextsiz next_extent_size,
hex(partnum)
partnum
from
systabnames, sysptnext, systabinfo, syspaghdr
where
dbsname != "HASHTEMP"
and tabname not like "sys
%"
and partnum = pe_partnum
and partnum = ti_partnum
and pg_partnum = ((trunc(partnum/1048576,0)
*1048576)+1)
and pg_pagenum = (partnum-(trunc(partnum/1048576,0)
*1048576))
group by 1, 2, 3, 6, 7, 8, 9, 10, 11, 12, 13
order by 3 asc, 4 desc
Ttfn