Search text in stored procedure in SQL Server

DharaPPatel picture DharaPPatel · Feb 5, 2013 · Viewed 1.5M times · Source

I want to search a text from all my database stored procedures. I use the below SQL:

SELECT DISTINCT
       o.name AS Object_Name,
       o.type_desc
  FROM sys.sql_modules m
       INNER JOIN
       sys.objects o
         ON m.object_id = o.object_id
 WHERE m.definition Like '%[ABD]%';

I want to search for [ABD] in all stored procedures including square brackets, but it's not giving the proper result. How can I change my query to achieve this?

Answer

Mahmoud Gamal picture Mahmoud Gamal · Feb 5, 2013

Escape the square brackets:

...
WHERE m.definition Like '%\[ABD\]%' ESCAPE '\'

Then the square brackets will be treated as a string literals not as wild cards.