CODEWITHSUNDEEP
sql-server
user292848
user292848

Reputation: 31

Select Query using like clause in Sql server 2008

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%'

Upvotes: 3

Views: 15418

Answers (2)

Anil Soman
Anil Soman

Reputation: 2467

You have to use escape character '\' in your query and also mention it at the end. You have used forward slash instead of backward one. In fact you can use any character as escape char, but you have to mention at the end.

Try this query

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

Upvotes: -1

gbn
gbn

Reputation: 432667

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.

Upvotes: 3

Related Questions