修改SQL Server中用作列默认值的函数

Question

我们遇到了一个SQL函数编码不佳的问题（虽然在生产环境中它能够正常工作，但在测试环境中却不能）。该函数用于在许多表中提供默认值，试图更改此函数会返回“Cannot ALTER ### because it is being referenced by object”的错误信息。

有没有解决这个错误信息的方法？我唯一能想到的方法是尝试编写脚本，将其从每个具有默认值的表中删除，修改该函数后再重新添加它。

- ChrisFletcher

Answer 1

由于该对象已被引用，因此您无法修改它。以下是操作步骤：

1. 从表/列中删除默认约束
2. 修改函数
3. 重新添加默认约束

- Raj More

Answer 2

如果一个函数绑定到列的默认约束条件上，SQL Server将不允许您修改该函数。

您唯一的选择是在修改函数之前删除约束条件。（来源）

- Daniel Vassallo

Answer 3

由于您需要：

1. 删除包含您的函数的所有默认约束
2. 修改您的函数
3. 然后重新插入所有已删除的约束

您可以将包含您将要删除/重新插入的函数的约束存储在一个临时表中，如：

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())';

然后执行步骤1（删除所有包含您的函数的默认约束）。

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;

第二步（修改你的函数）

ALTER FUNCTION my_function()
RETURNS -- return type
AS
BEGIN
   -- modify code
   RETURN -- return value
END

第三步（重新插入所有已删除的约束）

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;

不要忘记删除临时表

DROP TABLE temp_table;

- Sergio Ponce Domínguez