It can be very frustrating trying to track down the stored procedure where certain fields are updated when you are working with a database that you didn't create and is poorly documented (which basically describes every database I deal with).
To help cope with that, I wrote a simple script that locates the presence of a string in any stored procedure, in any database for the instance you are connected to.
Removing the OBJECTPROPERTY call in the DSQL statement will cause it to search through all object definitions including tables and views...
-- ********************************** Place Search Term Here *************************************--
DECLARE @findtext AS VARCHAR(1000) = 'text'
-- ********************************** Place Search Term Here *************************************--
IF Object_id(N'tempdb..##tbl') IS NOT NULL
BEGIN
DROP TABLE ##tbl
END
CREATE TABLE ##tbl
(
objname VARCHAR(1000)
)
DECLARE @cmd AS NVARCHAR(4000)
SET @cmd = ' USE [?] INSERT INTO ##tbl SELECT ''[?].['' + max(OBJECT_SCHEMA_NAME(id)) + ''].['' + OBJECT_NAME(id) + '']'' AS found_in FROM syscomments WHERE [text] LIKE ''%' + @findtext
+ '%'' AND OBJECTPROPERTY(id, ''IsProcedure'') = 1 GROUP BY OBJECT_NAME(id)'
EXEC Sp_msforeachdb
@cmd
SELECT *
FROM ##tbl
ORDER BY objname
IF Object_id(N'tempdb..##tbl') IS NOT NULL
BEGIN
DROP TABLE ##tbl
END
