System objects

If we want to find text in a stored proc definition, we can use this
select * from sysobjects where id in (
select id from syscomments where text like '%TextToFind%')

This approach seems to be outdated though. These system tables were available in SQL Server 2000, and are available in SQL Server 2005/8 as compatibility views for backward compatibility.

The recommend approach is to use the new system views such as sys.sql_modules and sys.procedures. The above query can now be written as
select * from sys.procedures
where object_definition(object_id) like '%TextToFind%'

Reference:
http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html