To quickly look through your stored procedure objects for a text value in a database instance, do the following:
use [Metro]
go
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%whatyouarelookingfor%'
AND ROUTINE_TYPE = 'PROCEDURE'
Now, this is no replacement for a configuration management repository, but it will do in a pinch. BTW, remember this sql statement does NOT transcend all databases on the server, just a single db instance.