Sometimes we need to find some objects used in stored procedure or similar other requirement we need to search some text used in Stored procedure. The following lines of code is very useful in for it.
SELECT DISTINCT OBJECT_NAME(id) FROM syscomments WHERE [text] LIKE '%YOURTEXT%'
Replace YOURTEXT with your actual text to be searched.
