10

How do I find a stored procedure in a Sybase database given a text string that appears somewhere in the proc? I want to see if any other proc in the db has similar logic to the one I'm looking at, and I think I have a pretty unique search string (literal)

Edit:

I'm using Sybase version 11.2

Danny Beckett
  • 20,529
  • 24
  • 107
  • 134
Oskar
  • 2,234
  • 5
  • 28
  • 35

6 Answers6

15

Two variations on Graeme's answer (So this also won't work on 11.2):

This lists the name of the sproc too, but will return multiple rows for each sproc if the text appears several times:

select object_name(id),* from syscomments 
   where texttype = 0 and text like '%whatever%'

This lists each sproc just once:

select distinct object_name(id) from syscomments 
   where texttype = 0 and text like '%whatever%'
AdamH
  • 1,331
  • 7
  • 19
8

In SQL Anywhere and Sybase IQ:

select * from SYS.SYSPROCEDURE where proc_defn like '%whatever%'

I'm not that familiar with ASE, but according to the docs (available from sybooks.sybase.com), it's something like:

select * from syscomments where texttype = 0 and text like '%whatever%'
Graeme Perrow
  • 56,086
  • 21
  • 82
  • 121
  • probably the correct answer, but my db doesn't have that column. I'm running 11.2 on this box (very old indeed) which might be the cause - any other thoughts? – Oskar Oct 30 '08 at 16:43
  • Note that "Sybase" is the name of a company, not a product. You are using Sybase Adaptive Server Enterprise (ASE) 11.2. – Graeme Perrow Oct 30 '08 at 17:13
6
select *  from sysobjects where 
    id in ( select distinct (id) from syscomments where text like '%SearchTerm%')
    and xtype = 'P'
Cosmin
  • 21,216
  • 5
  • 45
  • 60
Tom
  • 61
  • 1
  • 1
  • Leave the xtype filter off and you have an easy way to search across all database objects that have this text -- useful if you are considering dropping a function or procedure. – Erica Kane Jun 29 '16 at 20:21
6

Please remember, that text column in syscomments is varchar(255), so one big procedure can consist of many lines in syscomments, thus, the above selects will not find the procedure name if it has been splitted into 2 text rows in syscomments.

I suggest the following select, which will handle the above case:

declare @text varchar(100)
select @text        = "%whatever%"

select distinct o.name object
from sysobjects o,
    syscomments c
where o.id=c.id
and o.type='P'
and (c.text like @text
or  exists(
    select 1 from syscomments c2 
        where c.id=c2.id 
        and c.colid+1=c2.colid 
        and right(c.text,100)+ substring(c2.text, 1, 100) like @text 
    )
)
order by 1

-- kudos for this go to the creator of ASEisql

B0rG
  • 1,215
  • 12
  • 13
3
select distinct object_name(syscomments.id) 'SearchText', syscomments.id from syscomments ,sysobjects 
   where texttype = 0 and text like '%SearchText%' and syscomments.id=sysobjects.id and sysobjects.type='P'
Nishad
  • 426
  • 2
  • 7
  • 20
0

Multiple rows are used to store text for database objects the value might be accross two rows. So the more accurate answer is:

select distinct object_name(sc1.id)
from syscomments sc1
left join syscomments sc2
on (sc2.id = sc1.id and 
sc2.number = sc1.number and
sc2.colid2 = sc1.colid2 + ((sc1.colid + 1) / 32768) and
sc2.colid = (sc1.colid + 1) % 32768)
where
sc1.texttype = 0 and
sc2.texttype = 0 and
lower(sc1.text + sc2.text) like lower('%' ||     @textSearched || '%')
Fadi Hatem
  • 21
  • 1