Here is some T-SQL I got from a friend of mine (David Mccoullgh).
Just set the @SearchPhrase to search across stored proc, functions, triggers, views to see where that string is in use.
Very helpful if you are changing a field name and want to know where it is in use.
DECLARE @SearchPhrase varchar(1000)
SET @SearchPhrase = ‘ProductCode’
SELECT DISTINCT sysobjects.name AS [Object Name],
when sysobjects.xtype = ‘P’ then ‘Stored Proc’
when sysobjects.xtype = ‘TF’ then ‘Function’
when sysobjects.xtype = ‘TR’ then ‘Trigger’
when sysobjects.xtype = ‘V’ then ‘View’
end as [Object Type],
FROM sysobjects ParentTable
WHERE ParentTable.id = sysobjects.Parent_obj) ParentTable
FROM sysobjects, syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in (‘P’,’TF’,’TR’, ‘V’)
AND sysobjects.category = 0
AND CHARINDEX(@SearchPhrase, syscomments.text) > 0