Search text in stored procedure in SQL Server

Question

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 1

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.