remcok

-- Set search text
DECLARE @searchTerm varchar(MAX)
SET @searchTerm = '<SEARCH_TERM>' 

-- Stored Procedures
SELECT OBJECT_NAME(M.object_id) as sp_name, M.*
FROM sys.sql_modules M
JOIN sys.procedures P
ON M.object_id = P.object_id
WHERE M.definition LIKE @searchTerm