How to find a String in a Store Procedure?

select p.name from sys.procedures p join 
syscomments c ON c.id=p.object_id
where text like '%TABLENAME%'

To search for a column name in a table use the following:

 Select t.name as tablename,c.* from sys.columns c 
 Join sys.tables t on c.object_id=t.object_id
 where c.name = 'Note'