Search text in stored procedure in SQL Server

Question

I want to search for 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 1

In this case, ESCAPE the square brackets. Now, the square brackets will be considered as string literals, not as the wild cards.

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

You can learn in-depth about SQL statements, queries and become proficient in SQL queries by enrolling in our industry-recognized SQL training online.