I have often needed to find a stored procedure, view, trigger, and function that contains a certain piece of text, such as a text in a subject line.
Typically, people are trying to find all the stored procedures that reference a specific object. The best place to do this kind of searching is through your source control tool (you do keep your database objects in source control, don't you?), there are certainly some ways to do this in the database.
Using the code
Let's say you are searching for 'foobar' in all your stored procedures, view, trigger, and function. You can do this using syscomments:
DECLARE @SEARCHSTRING VARCHAR(255), @notcontain Varchar(255)
SELECT @SEARCHSTRING = 'foobar', @notcontain = ''
SELECT DISTINCT sysobjects.name AS [Object Name] ,
case when sysobjects.xtype = 'P' then 'Stored Proc'
when sysobjects.xtype = 'TF' then 'Function'
when sysobjects.xtype = 'TR' then 'Trigger'
when sysobjects.xtype = 'V' then 'View'
end as [Object Type]
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR','V')
AND sysobjects.category = 0
If you want to remove some string from searching you can always change the variable value "
@notcontain" to the corresponding value, example (remove word "comment" in the search above):
SELECT @SEARCHSTRING = 'foobar', @notcontain = 'comment'