CODEWITHSUNDEEP
stored-proceduressap-ase
Oskar
Oskar

Reputation: 2314

Find Sybase stored procedure in db given a text string that appears in the proc

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

Upvotes: 10

Views: 65532

Answers (6)

Fadi Hatem
Fadi Hatem

Reputation: 21

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 || '%')

Upvotes: 0

Nishad
Nishad

Reputation: 426

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'

Upvotes: 3

Tom
Tom

Reputation: 61

select *  from sysobjects where 
    id in ( select distinct (id) from syscomments where text like '%SearchTerm%')
    and xtype = 'P'

Upvotes: 6

B0rG
B0rG

Reputation: 1225

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

Upvotes: 6

AdamH
AdamH

Reputation: 1351

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%'

Upvotes: 15

Graeme Perrow
Graeme Perrow

Reputation: 57268

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%'

Upvotes: 8

Related Questions