My Piece of Mind

SQL SERVER 2005 – How To Search Strings In All The Stored Procedure Of Your Database July 15, 2011

I had a requirement that actually made me for a paticular string in all of my STORED PROCEDURE in my database.
After doing a little googling i came across this little snippet of query which was the simplest to use and understand.
You can also use it for serching , just replace the string “Any String You Want To Search” with any string you want to search and wallah .

SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE ‘%Any String You Want To Search%’
AND OBJECTPROPERTY(id, ‘IsProcedure’) = 1
GROUP BY OBJECT_NAME(id)