How to find specific text within SQL Server stored procedures?

Data and Databases
1

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

SELECT DISTINCT
    o.name AS Procedure_Name,
    o.type_desc
FROM sys.sql_modules m
JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition LIKE '%[XYZ]%';

The problem is, I’m searching for ‘[XYZ]’ (including the square brackets), but I’m not getting the results I expect. It seems like the LIKE operator isn’t handling the square brackets correctly. Does anyone know how to modify this query so it finds the exact string I’m looking for in all the stored procedures? I’ve been scratching my head over this for a while now.

2

have u tried using ESCAPE to handle the square brackets? something like this:

SELECT … WHERE m.definition LIKE ‘%[XYZ]%’ ESCAPE ‘[’

this tells SQL to treat [ as a literal char. might help with your search? let me know if it works for u!

3

I’ve encountered similar issues when searching for specific strings in stored procedures. One effective approach I’ve used is the PATINDEX function combined with a wildcard pattern. Here’s a query that might solve your problem:

SELECT DISTINCT o.name AS Procedure_Name, o.type_desc
FROM sys.sql_modules m
JOIN sys.objects o ON m.object_id = o.object_id
WHERE PATINDEX(‘%[XYZ]%’, m.definition) > 0;

This method allows for precise matching of ‘[XYZ]’ without the complications of LIKE’s bracket interpretation. It’s been reliable in my experience for finding exact strings within procedure definitions. Give it a try and see if it yields the results you’re expecting.

4

hey SwiftCoder15, have u considered using CHARINDEX instead? it’s more straightforward for exact matches:

SELECT DISTINCT o.name, o.type_desc
FROM sys.sql_modules m
JOIN sys.objects o ON m.object_id = o.object_id
WHERE CHARINDEX('[XYZ]', m.definition) > 0;

This should find ur exact string. lmk if it helps!