Search a string in all stored procedures

-- To search string "repid" through all stored procedures this limits to 4000 characters

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%repid%'
AND ROUTINE_TYPE='PROCEDURE'

-- To search string "repid" through all stored procedures

SELECT
sys.objects.name,
sys.objects.type_desc,
sys.sql_modules.definition
FROM
sys.sql_modules inner join sys.objects
on sys.sql_modules.object_id = sys.objects.object_id
WHERE
sys.sql_modules.definition LIKE '%tblShipToMaster%'