Today I was having a requirement where I need to search a text in all the stored procedures in my database. Basically I was in a need to get the list of all those stored procedures which contains a particular text. So I decided to blog for it. Below is the script for it:
SELECT [ROUTINE_SCHEMA] ,
[ROUTINE_NAME] AS [PROCEDURE NAME] ,
WHERE [ROUTINE_DEFINITION] LIKE '%searchtext%'
AND [ROUTINE_TYPE] = 'PROCEDURE'
ORDER BY [ROUTINE_SCHEMA] ,
I have attached the screenshot below for the results which I was getting when I run the above query with "add" as the search text on tempdb database.
EDIT: Information_Schema has a limit of 4000 charcters so you can use the following query then:
SELECT o.type_desc AS ROUTINE_TYPE ,
o.[name] AS ROUTINE_NAME ,
m.definition AS ROUTINE_DEFINITION
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o ON m.object_id = o.object_id
WHERE m.definition LIKE '%searchtext%'