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.
Since the object is referenced, you cannot modify it. This is what you do:
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)
Since you need to
You can store in a temporal table the constrains that you are going to delete/reinsert that contains your function, like
CREATE TABLE temp_table (query_delete VARCHAR(1000), query_create VARCHAR(1000)); INSERT INTO temp_table (query_delete, query_create) SELECT 'ALTER TABLE ' + schema_name(t.schema_id) + '.' + t.[name] + ' DROP CONSTRAINT ' + [constraint].[name] + ';' as query_delete, 'ALTER TABLE ' + schema_name(t.schema_id) + '.' + t.[name] + ' ADD CONSTRAINT ' + [constraint].[name] + ' DEFAULT my_function() FOR ' + [column].[name] + ';' as query_create FROM sys.default_constraints [constraint] LEFT OUTER JOIN sys.objects t ON [constraint].parent_object_id = t.object_id LEFT OUTER JOIN sys.all_columns [column] ON [constraint].parent_column_id = [column].column_id AND [constraint].parent_object_id = [column].object_id WHERE [constraint].[definition] = '(my_function())'; then performs step 1 (delete all default constrains that contains your function)
DECLARE table_cursor CURSOR FOR SELECT query_delete, query_create FROM temp_table; DECLARE @query_delete VARCHAR(1000), @query_create VARCHAR(1000); OPEN table_cursor; FETCH NEXT FROM table_cursor INTO @query_delete, @query_create; WHILE @@FETCH_STATUS = 0 BEGIN PRINT @query_delete; --PRINT @query_create; EXEC (@query_delete); FETCH NEXT FROM table_cursor INTO @query_delete, @query_create; END; CLOSE table_cursor; DEALLOCATE table_cursor; step 2 (modify your function)
ALTER FUNCTION my_function() RETURNS -- return type AS BEGIN -- modify code RETURN -- return value END step 3 (reinsert all those deleted constrains)
DECLARE table_cursor CURSOR FOR SELECT query_delete, query_create FROM temp_table; DECLARE @query_delete VARCHAR(1000), @query_create VARCHAR(1000); OPEN table_cursor; FETCH NEXT FROM table_cursor INTO @query_delete, @query_create; WHILE @@FETCH_STATUS = 0 BEGIN --PRINT @query_delete; PRINT @query_create; EXEC (@query_create); FETCH NEXT FROM table_cursor INTO @query_delete, @query_create; END; CLOSE table_cursor; DEALLOCATE table_cursor; don't forget to delete the temporal table
DROP TABLE temp_table; 
