Find a string by searching all stored procs in SQL Server Management Studio 2008
Sql ServerSsmsSql Server Problem Overview
Is there a way to search for a string within all stored procs in SQL Server Management Studio?
Sql Server Solutions
Solution 1 - Sql Server
SELECT *
FROM sys.sql_modules
WHERE definition LIKE '%yourstring%'
Solution 2 - Sql Server
Have a look at RedGate's SQL Search. It's a Management Studio plugin and a free download. You can search within a given database or across an entire instance.
Solution 3 - Sql Server
I always use this;
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%SEARCHSTRING%'
Solution 4 - Sql Server
In my case, I was looking to get the schema and the name of the stored procedure whenever I search for a specific text or keyword. The code I use and it is working for me is:
USE [your_DB_name];
GO
SELECT [Scehma]=schema_name(o.schema_id), o.Name
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON o.object_id = m.object_id
WHERE m.definition like '%your keyword%'
GO
The result is simple and as follows:
----------------------------------------------
| Schema | Name |
----------------------------------------------
| dbo | stored_procedure_name |
----------------------------------------------
...
and so on (if the keyword exists in more than one stored procedure)