SQL Search - free tool for searching your SQL Server

John, the "many rows" problem is only true if you are still using SQL Server 2000.  In SQL Server 2005 and up, the sys.sql_modules.definition column is actually NVARCHAR(MAX), and you will only ever find one row per object, so the string splitting part isn't an issue.  You can also use the OBJECT_DEFINITION() function, which returns a single NVARCHAR(MAX) value as well.

I wrote about how I do this in my environment here:

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/11/09/18684.aspx

That said, I do agree that for a free tool, this one is definitely worth a look.  It does have several features that would be difficult to reproduce with native tools and without a lot of hard work.

Too bad SQL Search doesn't work on SQL 2000, only on 2005/2008

but it's such a nice add-on to have for SSMS

jerryhung, you could always search syscomments for stored procedure text, the id column is the object_id.

select * from syscomments where text like '%SPText%'

John,

Does it have some intelligence built-in like to skip the text which is part of comments?

@Dan,

Even better, you can use information_schema views but that would be crude search