This one is really handy to search for any object in your SQL Server database. Very useful if you want to quickly check that a stored procedure or view is being used elsewhere etc. Hope you find it useful too!
SELECT
syo.name
FROM syscomments syc
INNER JOIN sysobjects syo ON
syo.id = syc.id
WHERE syc.[text] LIKE '%DROP PROC%'
