In a discussion on the MSDN Forums I had posted my original code to search a Stored Procedure (search any object within SQL really) for specific text (http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/847b421d-332d-40f8-9649-b26fc2306920). You can view my original blog post describing this code block at: http://sqln.blogspot.com/2008/10/searching-text-of-programmable-objects.html.
Adam Haines had posted a wonderful enhancement to my T-SQL code. He had returned the matching text result as an XML instead of a text, this allows the user to click on the returned text and have the resulting text appear in a new window.
The new code, with the XML returned results, is:
SET @StringToSearch = 'ENTER_SEARCH_TEXT_HERE'
OBJECT_DEFINITION(obj2.object_id) AS [text()]
from sys.all_objects obj2
where obj2.object_id = obj1.object_id
FOR XML PATH(''), TYPE
) AS Obj_text,
[type] as ObjType
FROM sys.all_objects obj1
WHERE OBJECT_DEFINITION(object_id([name])) LIKE '%' + @StringToSearch + '%'
--Optional to limit search results....
--AND [is_ms_shipped] = 0 --Only search user & post SQL release objects
--Change above to 1 if want to search only Microsoft provided objects released with SQL Server
--AND [type] = 'P' --Search only Stored Procedures
--AND [type] <> 'V' --Exclude Views
--See referenced article for listing of additional object types
Thanks again for this enhancement Adam!
Until next time, Happy Coding!!!