I answered this question on the MSDN forums: How can I search all my sprocs to see if any use a function?
Several people suggested using sp_depends. You can't really depend on sp_depends because of deferred name resolution. Take a look at this
First create this proc
CREATE PROC SomeTestProc
now create this function
CREATE FUNCTION somefuction(@id int)
SELECT @id = 1
now run this
result: Object does not reference any object, and no objects reference it.
Most people will not create a proc before they have created the function. So when does this behavior rear its ugly head? When you script out all the objects in a database, if the function or any objects referenced by an object are created after the object that references them then sp_depends won't be 100% correct
SQL Server 2005 makes it pretty easy to do it yourself
WHERE object_definition(object_id(specific_name)) LIKE '%somefuction%'
AND routine_type = 'procedure'
BTW somefuction is not a type, I already had a somefunction but was too lazy to change more than one character