Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Modifying a function used as a column default in SQL Server

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 is 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.

like image 393
ChrisFletcher Avatar asked Jan 22 '10 14:01

ChrisFletcher


2 Answers

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

  1. Remove the default constraint from the table/column
  2. Modify the function
  3. Add the default constraint back
like image 177
Raj More Avatar answered Oct 18 '22 15:10

Raj More


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)

like image 24
Daniel Vassallo Avatar answered Oct 18 '22 15:10

Daniel Vassallo