Search some text from Stored Procedure

If we want to search the stored procedure name for the text then we can try

Select Distinct SO.Name from sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id=SC.ID and SO.Type = 'P' AND SC.Text LIKE '%%distinct%city from dmslocation%' order by SO.Name