由于您需要:
- 删除包含您的函数的所有默认约束
- 修改您的函数
- 然后重新插入所有已删除的约束
您可以将包含您将要删除/重新插入的函数的约束存储在一个临时表中,如:
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;
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
AS
BEGIN
RETURN
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_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;