How to find specific text in 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. I’ve got a query that’s supposed to do this, but it’s not working as expected. Here’s what I’m using:

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

The problem is, I’m looking for the exact text ‘[ABD]’ with the square brackets, but my query isn’t finding it. Does anyone know how to tweak this so it finds the exact string, brackets and all? I’m pretty sure it has something to do with how SQL Server treats square brackets in LIKE clauses, but I’m stumped on how to fix it. Any help would be awesome!

2

Hey GrowingTree, i’ve run into this before. Try using LIKE ‘%[ABD]%’ instead. The extra brackets escape the special meaning. also, you might wanna add WHERE o.type = ‘P’ to filter for just procedures. hope this helps!

3

I’ve encountered this issue before, and there’s a simple solution. Instead of using LIKE, you can use the CHARINDEX function, which allows for exact string matching without worrying about special characters. Here’s a modified version of your query that should work:

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 CHARINDEX(‘[ABD]’, m.definition) > 0;

This approach is more straightforward and avoids the complexities of escaping special characters in LIKE clauses. It’s also generally faster for exact string matches. Remember to add WHERE o.type = ‘P’ if you want to limit the search to stored procedures only.