stored procedure question

K

kcwallace

Well-known member
Joined
May 27, 2004
Messages
172
Location
Irvine, CA
Is there a way to search the text of all of my stored procedures for a phrase? For example, all of them that contain a certain TableName?

Either built into SQL Server, or with code.
 
Unless the stored procs have been encrypted the actual text should be visible in the syscomments table. You should be able to query it with something like
Code: 
SELECT OBJECT_NAME(id) FROM syscomments 
WHERE [text] LIKE %TableName% AND OBJECTPROPERTY(id, IsProcedure) = 1
GROUP BY OBJECT_NAME(id)

if you are using sql2005 you could also use something like
Code: 
SELECT Name 
    FROM sys.procedures 
    WHERE OBJECT_DEFINITION(object_id) LIKE %TableName%
 
If you have your procs in a Visual Studio Database project you also have the advantage of doing a search using regular expressions, which can better isolate table names.

PDs example is what I generally use when I want a quick check.

-ner
 
Back
Top