Search String in SQL server

SELECT DISTINCT O.NAME, O.XTYPE
FROM SYSCOMMENTS C
INNER JOIN SYSOBJECTS O ON C.ID= O.ID
WHERE C.TEXT LIKE '%NAME OF SP%'