Search text in stored procedure in SQL Server

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?

I usually run the following to achieve that:

select distinct object_name(id)
from syscomments
where text like '%[ABD]%'
order by object_name(id)

Using [CHARINDEX](

[To see links please register here]

):

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 ;

Using [PATINDEX](

[To see links please register here]

):

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 PATINDEX('[[]ABD]',m.definition) >0 ;

Using this double `[[]ABD]` is similar to escaping :

WHERE m.definition LIKE '%[[]ABD]%'

Also you can use:


SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%flags.%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)

Thats include comments

select top 10 * from
sys.procedures
where object_definition(object_id) like '%\[ABD\]%'

Have you tried using some of the third party tools to do the search? There are several available out there that are free and that saved me a ton of time in the past.

Below are two SSMS Addins I used with good success.

[ApexSQL Search][1] – Searches both schema and data in databases and has additional features such as dependency tracking and more…

[SSMS Tools pack][2] – Has same search functionality as previous one and several other cool features. Not free for SQL Server 2012 but still very affordable.

I know this answer is not 100% related to the questions (which was more specific) but hopefully others will find this useful.


[1]:

[To see links please register here]

[2]:

[To see links please register here]

You can also use this one:

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION like '%Search_String%'

[Redgate's SQL Search][1] is a great tool for doing this, it's a free plugin for SSMS.




[1]:

[To see links please register here]

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

You can also use

CREATE PROCEDURE [Search](
@Filter nvarchar(max)
)
AS
BEGIN

SELECT name
FROM procedures
WHERE definition LIKE '%'+@Filter+'%'

END

and then run

exec [Search] 'text'