SQL Server , How to search for text in Stored Procedures

In this example, I searched for any SP containing the text LoadByIP:


 

SELECT Distinct SO.Name

FROM sysobjects SO (NOLOCK)

INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID

AND SO.Type = 'P'

AND SC.Text LIKE '%LoadByIP%'

ORDER BY SO.Name

 

 

Update: this was suggested by nts (see comments):

 

SELECT routine_name, routine_definition

FROM information_schema.routines

WHERE routine_definition LIKE '%LoadByIP%'

AND routine_type = 'PROCEDURE'

Both work well :-)

posted @ Wednesday, September 16, 2009 2:32 PM
Print

Comments on this entry:

# re: SQL Server , How to search for text in Stored Procedures

Left by nts at 9/18/2009 1:03 PM
Gravatar
nice will test this later, i use :- SELECT routine_name, routine_definition
FROM information_schema.routines
WHERE routine_definition LIKE '%textyoursearchingfor%'
AND routine_type = 'PROCEDURE'

# To:nts

Left by Angel Eyes at 9/21/2009 8:01 AM
Gravatar
Nice, I like it. I'm going to add it to this post :-)

# re: SQL Server , How to search for text in Stored Procedures

Left by haber at 12/15/2010 2:08 PM
Gravatar
For developpers: option to fully clear the cache before every page-request
- Option for disabling cookie from not visited domains (like safari)
- Option for disabling resizing of the current window for scripts. Definetly no web app need 1920x1200.
- Sometime there is the case

http://www.bighaber.com
Comments have been closed on this topic.