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.

Answer 2

To search for a specific text pattern, including square brackets, within all stored procedures in SQL Server, the query needs to be adjusted to handle the special characters in the search pattern correctly, as the previous query is able to search the ABD characters but the modifications are to be made in such a way so that the brackets can also be a part of the pattern matching ‘[‘,’]’, This can happen by using the Escape character in the starting of the bracket and after the ending of the characters  ABD so, that they can be treated as a literal character in the search pattern, In SQL server, to search for special characters like square brackets, you need to use the character ‘\’ before them.

Here’s how you can modify your query to search for the text ‘[ABD]’ including the square brackets:

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\]%';

Answer 3

When looking for a certain text pattern found within square brackets in an SQL Server stored procedure, it is essential to write the LIKE clause in such a manner that the square brackets are escaped. In SQL Server, there is more than one way of escaping square brackets, the commonest ways being using two in a row to escape the preceding one or surrounding bracket escaping with half pairs, use of [[ and ]].

If you want to implement follow this:

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