Modifying a function used as a column default in SQL Server

Question

Hi, We're having an issue with a poorly coded SQL function(which works fine in live, but not in our test environment). This function is used to provide a default value in many tables, trying to ALTER the function returns a "Cannot ALTER ### because it is being referenced by object" error.

Is there any way round this error message? The only way I can think of us attempting to write a script to remove it from every table that has it as a default, altering the function and re-adding it after.

Thanks

Answer 1

SQL Server will not allow you to modify a function that is bound to the DEFAULT constraint of a column.

Your only option is to remove the constraint before altering the function. (Source)

Answer 2

Since the object is referenced, you cannot modify it. This is what you do:

1. Remove the default constraint from the table/column
   • Modify the function
   • Add the default constraint back