Select Query using like clause in Sql server 2008

Question

Table name  : sp_text  
Column name : obj_Text

In this column, all the stored procedures are stored as text.

I need to retrieve all the stored procedures which has raiserror and %d in raiserror.

eg, A SP contains the following raiserror in it.

raiserror('quantity adjustment is not allowed in row no %d', 16, 1, @fprowno)  

I tried the below query but couldn't arrive at the result.

select *
from   sp_text_ismail 
where  obj_Text like '%raiserror%'
  and  obj_Text like '%/%d%'

Answer 1

Use the system view sys.sql_modules: far simpler. And you can escape % with brackets in the LIKE

SELECT OBJECT_NAME(object_id), * FROM sys.sql_modules
WHERE definition LIKE '%raiserror%[%]d%'

You also can not select or filter on a stored procedure.