Find Stored Proc using referencing a database object

When ever you are renaming a database object you want to make sure you have changed all the code referencing that object. Following is the T-SQL to find a phrase of word in all the database object codes:

DECLARE @Text AS NVARCHAR(50)

SET @Text = 'DBObjectName'

/*Set the variable with the DB Object name, Linked Server name, Database name or just a word string to be searched*/
SELECT DISTINCT sysobjects.NAME
 ,sysobjects.xtype
 ,SUBSTRING(syscomments.TEXT, CHARINDEX(@Text, syscomments.TEXT) - (
   CASE 
    WHEN CHARINDEX(@Text, syscomments.TEXT) < 100
     THEN CHARINDEX(@Text, syscomments.TEXT)
    ELSE 100
    END
   ), 200) AS Extract
FROM syscomments
INNER JOIN sysobjects ON syscomments.id = sysobjects.id
WHERE syscomments.TEXT LIKE '%' + @Text + '%'

It will give you the name of the object referencing it. Type of the object and also extract of the code around the name of the database object for your reference.