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
AS
SELECT
dbo.somefuction(1)
GO
now create this function
CREATE
FUNCTION somefuction(@id int)
RETURNS
int
AS
BEGIN
SELECT
@id = 1
RETURN
@id
END
Go
now run this
sp_depends
'somefuction'
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
SELECT
specific_name,*
FROM
information_schema.routines
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