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

You're most probably missing the escape of the square bracket. Try the code below and let me know how it works for you. Type in the keywords you're looking for in the @text_string variable:

DECLARE @text_string VARCHAR(MAX);

SET @text_string = '';

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 CONCAT('%\[', @text_string, '\]%') ESCAPE '\'

OR [m].[definition] LIKE CONCAT('%', @text_string, '%')