DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Mark has posted 2 posts at DZone. View Full User Profile

Search For Specific Text In All Stored Procedures

04.25.2007
| 13318 views |
  • submit to reddit
        
declare @search varchar(50)
SET @search = 'searchterm'

SELECT    
     ROUTINE_NAME,
     ROUTINE_DEFINITION
FROM    
    INFORMATION_SCHEMA.ROUTINES
WHERE    
    ROUTINE_DEFINITION LIKE @search
ORDER BY
    ROUTINE_NAME
    

Comments

Da Rickard replied on Thu, 2007/04/26 - 11:52am

Seems to work better if you wildcard the search term as shown below. That could also be done when setting the @search variable, but if you do it in the query you don't have to worry about it anymore. declare @search varchar(50) SET @search = 'searchterm' SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%' + @search + '%' ORDER BY ROUTINE_NAME