stored procedure question

[kcwallace]

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.

[PlausiblyDamp]

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

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

SELECT Name 
   FROM sys.procedures 
   WHERE OBJECT_DEFINITION(object_id) LIKE '%TableName%'

[Nerseus]

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.

 

PD's example is what I generally use when I want a quick check.

 

-ner