Here are some handy scripts in case you need to search for a particular text inside a stored procedure, trigger or constraint on SQL Server. This can come up for example if a new function has changed or your development team is working on some modification that might affect several objects. Just replace the wilcard (or the WHERE clause in general) and run on the database you’re searching on.

Search for text inside a stored procedure

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

Search for text inside a trigger

SELECT TR.NAME AS OBJECT_NAME, TR.TYPE_DESC, SM.DEFINITION FROM SYS.SQL_MODULES SM INNER JOIN SYS.TRIGGERS TR ON SM.OBJECT_ID=TR.OBJECT_ID WHERE SM.DEFINITION LIKE '%wildcard%'

Search for text inside a constraint

SELECT CONSTRAINT_NAME,CHECK_CLAUSE FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS WHERE CHECK_CLAUSE LIKE '%wildcard%'

Cheers!