473,289 Members | 1,866 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Search contents of stored procedures?

Hi All,

I'm wondering if anyone can tell me if it's possible to search for
stored procedures by their contents?

I tend to leave very descriptive notes in stored procedures and
they're beginning to build up in number, so I'm wondering if it's
possible to search in Query Analyzer for stored procedures that
contain certain words / phrases etc.

Alternatively, is there a way to do a system-level select that looks
something like the following pseudo-gibberish (actually, it's actual
gibberish, I guess, rather than pseudo-gibberish):

SELECT * FROM [all stored procedures] where [contents] like '%my
search phrase%'

Any help much appreciated!

Much warmth,

Murray
Jul 20 '05 #1
4 8954
On Fri, 16 Apr 2004 16:48:42 GMT, M Wells wrote:
Hi All,

I'm wondering if anyone can tell me if it's possible to search for
stored procedures by their contents?

I tend to leave very descriptive notes in stored procedures and
they're beginning to build up in number, so I'm wondering if it's
possible to search in Query Analyzer for stored procedures that
contain certain words / phrases etc.

Alternatively, is there a way to do a system-level select that looks
something like the following pseudo-gibberish (actually, it's actual
gibberish, I guess, rather than pseudo-gibberish):

SELECT * FROM [all stored procedures] where [contents] like '%my
search phrase%'

Any help much appreciated!

Much warmth,

Murray


Hi Murray,

If the stored procedures were not created "with encryption", you'll
find their text in syscomments.

SELECT object_name(id)
FROM syscomments
WHERE text LIKE '%your search phrase%'
Note 1: This will search all stored procedures, but also all triggers
and (if I recall correctly) all views.

Note 2: If the text of a stored procedure (or trigger or view) is
verly long (over 8,000 characters), SQL Server will split the text
over several rows in syscomments. The split might even be made right
in the middle of a word. This means that there is a chance that this
search will miss a procedure, if one 8,000 character group ends in
"your sea" and the next groups starts with "rch phrase". The chance
that this happens is, of course, very small.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #2
> I'm wondering if anyone can tell me if it's possible to search for
stored procedures by their contents?
<snip>
SELECT * FROM [all stored procedures] where [contents] like '%my
search phrase%'


<snip>

Murray,

In addition to Hugo's comments, I thought an internal application we created
might give you a few ideas... For searching stored procedures it isn't as
fast as the select Hugo presented, but can be extended for lots of SP
related tasks. Unfortunately, I can't supply source code.

Caveat: I know this works on SQL Server 2000, but haven't even tested or
checked what other versions of SQL Server this might or might not work
with...

We get all stored procedure names with:

SELECT name
FROM sysobjects
WHERE type = 'P' AND
name NOT LIKE 'dt_%'

Then when searching, we get the parameter info with the system stored
procedure sp_help and the text of the proc with sp_helptext. As Hugo
pointed out, you'll need to account for sp_helptext possibly returning
multiple records.

Although you can do lots of things with this technique, it's suboptimal (to
say the least) for searching the text of all stored procedures. If a simple
text search is what one of our developers is after, they use a search
utility ilke grep to scan the text files we maintain in source code control
for our stored procs.

Good Luck!

Craig
Jul 20 '05 #3
On Fri, 16 Apr 2004 21:30:54 GMT, "Craig Kelly"
<cn************@nospam.net> wrote:
I'm wondering if anyone can tell me if it's possible to search for
stored procedures by their contents?


<snip>

Thanks to both Hugo and Craig for your help!

Much warmth,

Murray

Jul 20 '05 #4
On Fri, 16 Apr 2004 21:30:54 GMT, "Craig Kelly"
<cn************@nospam.net> wrote:
I'm wondering if anyone can tell me if it's possible to search for
stored procedures by their contents?


<snip>

Thanks to both Hugo and Craig for your help!

Much warmth,

Murray

Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: todd | last post by:
here is a search tool SP I wrote. How many times have you wanted to search all your Stored procs or views (in a database) for a keyword but couldn't!? Well now you can! THis can makes life a...
1
by: Vien Nguyen | last post by:
Hi all, I need to be able to obtain the full contents, i.e. sql statements inside, of all procedures in SQl Server 2000. I am using MS type 4 JDBC driver. I know that from the database...
2
by: M Wells | last post by:
Hi All, I'm wondering if anyone can tell me if it's possible to search for stored procedures by their contents? I tend to leave very descriptive notes in stored procedures and they're...
3
by: Eugene | last post by:
Hi, Is there any way to find all stored procedures that contain a given field Example: I want to find all stored procedures that work with the field ShipDate in tblOrder table Thanks, Eugene
4
by: Axel | last post by:
Is it possible to write a Stored Procedure that takes a string of search keywords as argument and returns the recordset? At the mo I am passing the WHERE String as argument. I got this...
2
by: Kent Lewandowski | last post by:
hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered...
5
by: Tim Marshall | last post by:
I was following the thread "Re: Access Treeview - Is it Safe Yet?" with interest and on reading the post describing Lauren Quantrell's SmartTree, I've run into something I don't understand: Stored...
7
by: David Lozzi | last post by:
I need to support multiple keyword search, not boolean searchs, just simple searches, i.e. "marhsall ma". How is this done? Do I send the entire search string to the Proc? How do I deal with it...
2
by: COHENMARVIN | last post by:
I found that I'm making an error in my 'case' statements, and now I want to go through a database full of many stored procedures to find that error in some of them and to fix it. Is there any way...
2
by: Bobby Edward | last post by:
I am creating an Advanced Search form. The user can select whether their phrase will search the "Title", "Description" or "All" fields. Obviously the WHERE clause will change, depending on the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.