Thanks Jim for a very handy script. I did notice a few minor 'gotchas' though. Nothing major:
1. If the UDF name is included ONLY as a COMMENT in the stored procedure , it will still be returned
2. If there are UDFs with similar names, you will get a false return. For example: If you have 2 UDFs named fnEasterSunday and fnEasterSunday2, then fnEasterSunday2 will be returned as having a reference to fnEasterSunday.
I modified the code slightly to use sys.modules so I could also find any views or other UDFs that might reference the functions:
-- search all objects for that function name
SET @SQLCmd =
SELECT OBJECT_NAME(object_ID) AS Name, ''' + @fnName + ''' FROM sys.sql_modules WHERE [definition] LIKE ''%' + @fnName + '%'''
I found that I had to add a line after the WHILE loop finished to eliminate self-references:
DELETE #spWithFn WHERE spName=fnName
Thanks again for taking the time to create and share this very useful script.