Script to find and replace function

Question

Hi All,

We have changed some base functions we use in an application and I am trying to figure out what the best way would be for me to find and replace the existing function with the new one? The input / outputs don't change, just the schema and some of the internal logic.

Is there a good approach to finding the objects (views, procedures, etc) that use a function and then replace it with a new one?

I was thinking I could use sys.syscomments to find the objects then do a replace on the text of the object?

Any suggestions would be greatly appreciated.

Thanks,

S

Answer 1

To find references:

SELECT OBJECT_NAME(m.object_id), 
       m.*
  FROM SYS.SQL_MODULES m
 WHERE m.definition like N'%my_function_name%'

SYSCOMMENTS and INFORMATION_SCHEMA.routines have NVARCHAR(4000) columns. So if "my_function_name" is used at position 3998, it won't be found. SYSCOMMENTS does have multiple lines, but ROUTINES truncates.

Once you know where the references are, you can update them...

Answer 2

Or script everything out to an .sql file to identify the dependent objects and do the replace, if you want.

Just drop the dependent objects and create them with the updated script.