You can search a text column. You can't index it. Well OK you can. The Full-Text Indexing can index a text column.

SELECT OBJECT_NAME(id) FROM syscomments WHERE text LIKE '%procedure%'

-Mike