The sp_iqtablesize procedure is nice - but not helpful for scanning accross an entire instance and all its schemas. The script below will do the job: List all tables, ordered by the space they are taking up (in KB). Requirements: you must have the IQ client in your path (required both isql and dbisql).
#!/bin/bash
#=============================================
# iq_space_list.sh
#
# Change settings below for your server.
#=============================================
IQSERVER=HELENA
USERID=DBA
IQHOST=MONTANA.acme.com
IQPORT=4700
INTERFACES=$SYBASE/interfaces
fname=${IQSERVER}.space.txt
sqlname=${IQSERVER}.space.sql
echo "=============================================="
echo "Please enter DBA password for $IQSERVER"
read SECRET
echo "=============================================="
echo "Generate space SP calls .."
echo "=============================================="
isql -S $IQSERVER -U $USERID -P $SECRET -I $INTERFACES -w132 -o $fname << FINIS
set nocount on
go
declare @sp varchar(20)
select @sp = 'exec sp_iqtablesize'
select @sp as cmd, '''' + substring(user_name,1,30) + '.' + substring(table_name,1,66) + '''' + ' ' as name
from SYS.SYSTABLE t1,
SYS.SYSUSERPERM t2
where t2.user_id = t1.creator
order by 1
go
FINIS
echo "=============================================="
echo "Prep SQL File .."
echo "=============================================="
grep iqtable $fname | awk '{ print $0; print "go"; } ' > $sqlname
echo "=============================================="
echo "Run SQL File .. (might take a while) .. output is in $fname"
echo "=============================================="
dbisql -nogui -c "uid=$USERID;pwd=$SECRET;eng=$IQSERVER;dbn=$IQSERVER;links=tcpip{host=$IQHOST;port=$IQPORT}" $sqlname > $fname
echo "=============================================="
echo "Sort by size."
echo "=============================================="
cat $fname | egrep '[A-Z]' | egrep -v '\-\-\-' | egrep -v 'Tablename|First 1|econds' | awk '{ print $4 " " $1 "." $2 } ' | sort -n
echo "DONE."
|
|
