Kevin,
This is a little long winded but there are a number of options - the last one is the fastest to do.
In an ideal world, you have been saving the DDL to create it in the first place and can use the search function of an editor or write a small awk or perl script to get the function names out.
In a less ideal world, you can go into enterprise manager, select your database, right click on it (in the left pane), choose all tasks, and then generate sql script, and create a script for all the objects, triggers, stored procs, etc.. Then search the resulting script for your string.
A more labor intensive approach is to go into you database in query analyzer and execute:
select 'exec sp_helptext ' + name from sysobjects
where type in ('IF','P','TF','TR')
Then take the output except for the header and footer and execute it and then save the output and search it.
The easiest way - although there is an element of risk - is to go into your database and execute:
select o.name from sysobjects o, syscomments c
where o.type in ('IF','P','TF','TR')
and o.id = c.id
and c.text like '%AS ELIG_ID%'
This should give you what you want and you might even consider dropping the constraint of o.type. The risk is because if the text is more than 4000 char long, it gets stored in multiple records in syscomments. If this is the case and the string you are searching for happens to be where it breaks records, then you won't find that instance.
There are 2 caveats:
1: You should probably look in both master and the database you are working on because they can both contain stored procs which are accessible in the DB you are working on.
2: If someone else wrote the stored procs and wanted to hide their content, they can and I don't believe that you can do anything to find out the original text.
. . . Tom
Tom Zeblisky
Reuters
"Sexton, Kevin"
<kevin.[Email Address Removed] To: [Email Address Removed]
cemed.com> cc:
Subject: searching for string thru all procs.
02/02/02 10:25 AM
Header: Internal Use Only
I need to find all occurences of the string 'AS ELIG_ID' in my code
(triggers, functions, procs.)
This is not an Object Search.
-Kevin
---------------------------------------------------------------------
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed] Visit our Internet site at http://www.reuters.com
Any views expressed in this message are those of the individual
sender, except where the sender specifically states them to be
the views of Reuters Ltd.
MS Sql Server LazyDBA home page