I modified your last query a bit and obtained something that seems to be faster on SQL Server 2000:

SELECT distinct O.Name, O.Type

FROM

(

 select

 s1.id,

 cast(coalesce(s1.text, '') as varchar(8000)) +

 cast(coalesce(s2.text, '') as varchar(8000))

 as [text]

 from syscomments s1 left join syscomments s2

 on s2.id = s1.id and s2.colid = s1.colid + 1

) C

INNER JOIN sysobjects O ON C.id = O.Id

WHERE C.TEXT LIKE '%yourSearchString%'