In order to peek into the procedure cache, you can use the following dbcc command:

dbcc procbuf

In order to see the output on the console, use:

dbcc traceon(3604)
go
dbcc procbuf
go

You’ll see that the output is pretty extensive. If what you are after is which trigger and procedures are using space in the procedure cache and how much space it uses, you only are interested in the lines like:

...
    Total # of bytes used               : 1266320
...
pbname='sp_aux_getsize'   pbprocnum=1
...

You can thus execute it and grep for these two lines:

$SYBASE/OCS/bin/isql -Usa -Pxxxx << EOT | grep "pbname
Total # of bytes used"
dbcc traceon(3604)
go
dbcc procbuf
go
EOT

You of course need to replace xxxx by your actual password.

Then you will want to make it looks nicer:

• Merge the two lines: awk ‘!(NR%2){print p” “$0}{p=$0}’
• Display only the name and the size: awk ‘{ print $1″ “$9″ bytes”; }’ | sed “s/pbname=//g” | sed “s/’//g”
• Sort by size: sed “s/’//g” | sort -k2 -n

Putting it all together:

$SYBASE/OCS/bin/isql -Usa -Pxxxx << EOT | grep "pbname
Total # of bytes used" | awk '!(NR%2){print p" "$0}{p=$0}' | awk '{ print $1" "$9" bytes"; }' | sed "s/pbname=//g" | sed "s/'//g" | sort -k2 -n 
dbcc traceon(3604)
go
dbcc procbuf
go
EOT

You will then see something like:

...
sp_jdbc_tables 62880 bytes
sp_getmessage 68668 bytes
sp_aux_getsize 80596 bytes
sp_mda 81433 bytes
sp_mda 81433 bytes
sp_drv_column_default 90144 bytes
sp_dbcc_run_deletehistory 133993 bytes
sp_lock 180467 bytes
sp_helpsegment 181499 bytes
sp_dbcc_run_summaryreport 207470 bytes
sp_modifystats 315854 bytes
sp_autoformat 339825 bytes
sp_spaceused 353572 bytes
sp_jdbc_columns 380403 bytes
sp_do_poolconfig 491584 bytes
sp_configure 823283 bytes