Find a string by searching all stored procs in SQL Server Management Studio 2008

Solution 1:

SELECT *
FROM sys.sql_modules
WHERE definition LIKE '%yourstring%'

Solution 2:

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:

I always use this;

SELECT Name
 FROM sys.procedures
 WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%SEARCHSTRING%'

Solution 4:

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)