How to Get a particularize Text in SQL Server Stored Procedure (search Text on store procedure)

Recently I come across a issue .my one of subordinate getting a bulk mails as he is added his Email Id one of Store procedure for testing purpose and forgot to change with proper email id and now he is forgot to which SP he is added email Id.

For for that SQL 2008 provide the solution to find the Particular text on SP and search.

As

SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)

FROM sys.procedures

WHERE OBJECT_DEFINITION(object_id) LIKE '%Text%'

How to Find the Email/ Employee Name group wise. Comma separated List from Sql 2008
--How to Find the Email/ Employee Name group wise
IF(OBJECT_ID('tempdb.dbo.#TestTempEmail') > 0)
BEGIN
DROP TABLE #TestTempEMAIL
END
IF(OBJECT_ID('tempdb.dbo.#employeedetails') > 0)
BEGIN
DROP TABLE #employeedetail
END
Create table #TestTempEMAIL (EmployeeCode varchar(50),Type1 varchar(10))
insert into #TestTempEMAIL values
('Test0007','HR'),
('Test0027','HR'),
('Test0459','HR'),
('Test0011','Finance'),
('Test0078','Finance'),
('Test0237','Finance'),
('Test0289','Finance'),
('Test0029','IT'),
('Test0055','IT'),
('Test0239','IT'),
('Test0313','IT'),
('Test0052','Admin'),
('Test0010','Business'),
('Test0013','Business')
SELECT   EmployeeCode = STUFF((SELECT  ' '+EmployeeCode+ ' ,'
    FROM #TestTempEMAIL AS Test1 WHERE Type1 = Test.Type1
        ORDER BY Type1
     FOR XML PATH('')), 1, 1, ''),Type1,GETDATE()
from #TestTempEMAIL Test
GROUP BY Type1
ORDER BY Type1;

Output