Search contents of stored procedures?

[M Wells]

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

[Hugo Kornelis]

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)

[Craig Kelly]

> 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

[M Wells]

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

[M Wells]

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