Sometimes it’s handy to just conduct a simple search of your SQL server objects to locate a text string.

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
AND definition LIKE '%text%'

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'isView') = 1
AND definition LIKE '%text%'

SELECT OBJECT_NAME(object_id),definition
FROM sys.sql_modules
WHERE
(OBJECTPROPERTY(object_id, 'IsTableFunction') = 1 OR OBJECTPROPERTY(object_id, 'IsScalarFunction') = 1)
AND definition LIKE '%text%'

SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%MyName%'