Hi,
I want to generate the list of storedprocedures (in Sybase ASE 12.5)
that contain a particular word.Can anyone suggest some method for
accomplishing this?

Thanks,
Raj

select distinct object_name(id) from syscomments where text like
'%sysdatabases%'

order by object_name(id)

"Raj" <rasu90@yahoo.com> wrote in message
news:5d364c3b.0309032200.7bad80c6@posting.google.c om...
> Hi,
> I want to generate the list of storedprocedures (in Sybase ASE 12.5)
> that contain a particular word.Can anyone suggest some method for
> accomplishing this?
>
> Thanks,
> Raj

rasu90@yahoo.com (Raj) wrote in message news:<5d364c3b.0309032200.7bad80c6@posting.google. com>...
> Hi,
> I want to generate the list of storedprocedures (in Sybase ASE 12.5)
> that contain a particular word.Can anyone suggest some method for
> accomplishing this?
>
> Thanks,
> Raj

raj, you query the "text" column of the syscomments table for that
particular word or defncopy all the procs and use something like grep
to get all the occurrences of it.
regards,
M.

"Willie Kraatz" <wkraatz@csc.com> writes:

>select distinct object_name(id) from syscomments where text like
>'%sysdatabases%'

That will do stored procedures and maybe other things (I'm not sure).
Is there a way to do a comprehensive search of everything - stored
procedures, table names, column names, triggers, rules, everything
except the data?

--
Ed Avis <ed@membled.com>

You should note that syscomments does not split on word boundries so when
you do a search there is a possibilty that the word you are searching on is
split between two rows. The only 100% safe way is to consolidate the entire
text of the SP and then search it for the string you are searching on.


"Ed Avis" <ed@membled.com> wrote in message
news:l1y8x2qoxx.fsf@budvar.future-i.net...
> "Willie Kraatz" <wkraatz@csc.com> writes:
>
> >select distinct object_name(id) from syscomments where text like
> >'%sysdatabases%'
>
> That will do stored procedures and maybe other things (I'm not sure).
> Is there a way to do a comprehensive search of everything - stored
> procedures, table names, column names, triggers, rules, everything
> except the data?
>
> --
> Ed Avis <ed@membled.com>

Is it possible? I'll give the standard DBA aswer: "it depends".

Consider the source code for a table creation. It may contain the words
"smallint" or "not null". These won't be stored. The same for a view.

AFAIK almost everything else is stored somewhere. Msot of them are names in
sysobjects. But then there are sysusermessages.description,
sysattributes.object_cinfo, sysattributes.char_value,
sysattributes.comments, systypes.name, syssegments.name,
systhresholds.proc_name, etc. And if you are in the master database there
are also sysmessages.description, sysdatabases.name, syslogins.name,
sysprocesses.hostname, etc.

So, again, if you are not referring to source code but to "stored stuff"
then the answer is "yes". But you'll have to use a union or temporary table
in order to search all of the character columns in the system tables. Oh,
yes, there are also two text columns in the system tables (sysattributes &
sysxtypes) as well.

"Ed Avis" <ed@membled.com> wrote in message
news:l1y8x2qoxx.fsf@budvar.future-i.net...
> "Willie Kraatz" <wkraatz@csc.com> writes:
>
> >select distinct object_name(id) from syscomments where text like
> >'%sysdatabases%'
>
> That will do stored procedures and maybe other things (I'm not sure).
> Is there a way to do a comprehensive search of everything - stored
> procedures, table names, column names, triggers, rules, everything
> except the data?
>
> --
> Ed Avis <ed@membled.com>

There is a stored proc called sp__grep. You can find it at Ed Barlow's website
--
http://www.edbarlow.com/document/procs/index.htm.

It works very nicely.

David