Finding specific text patterns in SQL Server stored procedures

Data and Databases
1

I’m trying to locate a specific text pattern in all the stored procedures in my SQL Server database. Here’s the query I’m using:

SELECT DISTINCT
    obj.name AS ProcedureName,
    obj.type_desc AS ObjectType
FROM sys.sql_modules mod
    INNER JOIN sys.objects obj
    ON mod.object_id = obj.object_id
WHERE mod.definition LIKE '%some_text%';

The problem is, I need to find the exact pattern ‘[XYZ]’ (including the square brackets), but my query isn’t returning the expected results. How can I modify this query to accurately find this specific text pattern in all stored procedures? I’m wondering if there’s a special way to handle square brackets in the LIKE clause or if I need a different approach altogether. Any help would be appreciated!

2

hey there! have u tried using double brackets to escape those square brackets? like this: ‘%[[XYZ]]%’? it might do the trick! also, if ur database is case-sensitive, you could try wrapping both the definition and pattern in LOWER() for a case-insensitive search. what do u think? any luck with that approach?

3

To find the exact pattern ‘[XYZ]’ in your stored procedures, you need to escape the square brackets. SQL Server uses square brackets as special characters, so they need special handling. Modify your query like this:

SELECT DISTINCT
    obj.name AS ProcedureName,
    obj.type_desc AS ObjectType
FROM sys.sql_modules mod
    INNER JOIN sys.objects obj
    ON mod.object_id = obj.object_id
WHERE mod.definition LIKE '%[[XYZ]]%';

This double-brackets approach tells SQL Server to treat them as literal characters. If you need case-insensitive search, wrap both the definition and pattern in LOWER() or UPPER(). For large databases, consider indexing the definition column to improve search performance.

4

hey ava89, try escaping those brackets like this:

SELECT DISTINCT
obj.name AS ProcedureName,
obj.type_desc AS ObjectType
FROM sys.sql_modules mod
INNER JOIN sys.objects obj
ON mod.object_id = obj.object_id
WHERE mod.definition LIKE ‘%[[XYZ]]%’;

the double brackets tell sql server to treat em as literal characters. hope that helps!