数据库—修改某字段默认值

前言

有时候，数据库的字段默认值没有正确设置，这时候需要改默认值。以下是我做的改默认值的记录，希望对网友有所帮助。

1.SQL SERVER

下面的示例假设你要修改名为 YourColumnName 的字段，并为其设置一个新的默认值 NewDefaultValue。你需要根据实际情况替换 YourColumnName 和 NewDefaultValue。如果默认值是字符串，则在外层加单引号

DECLARE @TableName NVARCHAR(255),
        @ColumnName NVARCHAR(255),
        @ConstraintName NVARCHAR(255),
        @SqlCommand NVARCHAR(MAX);

-- 如果游标存在，先关闭并释放
IF CURSOR_STATUS('global', 'TableCursor') >= -1
BEGIN
    CLOSE TableCursor;
    DEALLOCATE TableCursor;
END

-- 游标遍历包含指定列的所有表
DECLARE TableCursor CURSOR FOR
SELECT t.name AS TableName, c.name AS ColumnName
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name = 'ATIME' 
  AND t.name IN (
    '',''
  )
  AND t.schema_id = SCHEMA_ID('bzk');

OPEN TableCursor;

FETCH NEXT FROM TableCursor INTO @TableName, @ColumnName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 获取该列上的默认约束名（精确匹配表和列）
    SELECT @ConstraintName = dc.name 
    FROM sys.default_constraints dc 
    INNER JOIN sys.tables t ON dc.parent_object_id = t.object_id
    INNER JOIN sys.columns c ON dc.parent_column_id = c.column_id AND dc.parent_object_id = c.object_id
    WHERE c.name = @ColumnName 
      AND t.name = @TableName 
      AND t.schema_id = SCHEMA_ID('bzk');

    -- 如果存在旧约束，安全删除
    IF @ConstraintName IS NOT NULL
    BEGIN
        SET @SqlCommand = '
IF EXISTS (SELECT 1 FROM sys.default_constraints 
           WHERE parent_object_id = OBJECT_ID(''[bzk].[' + @TableName + ']'') 
           AND name = ''' + @ConstraintName + ''')
BEGIN
    ALTER TABLE [bzk].[' + @TableName + '] DROP CONSTRAINT [' + @ConstraintName + '];
    PRINT ''已删除约束: ' + @ConstraintName + ''';
END';
        EXEC sp_executesql @SqlCommand;
    END

    -- 添加新的默认约束（使用规范命名）
    -- 先确保同名约束不存在（避免重复）
    SET @SqlCommand = '
IF NOT EXISTS (SELECT 1 FROM sys.default_constraints 
               WHERE parent_object_id = OBJECT_ID(''[bzk].[' + @TableName + ']'') 
               AND name = ''DF_' + @TableName + '_' + @ColumnName + ''')
BEGIN
    ALTER TABLE [bzk].[' + @TableName + '] 
    ADD CONSTRAINT [DF_' + @TableName + '_' + @ColumnName + '] 
    DEFAULT (CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 120), 120)) FOR [' + @ColumnName + '];
    PRINT ''已添加默认约束到 ' + @TableName + '.' + @ColumnName + ''';
END';
    EXEC sp_executesql @SqlCommand;

    FETCH NEXT FROM TableCursor INTO @TableName, @ColumnName;
END

CLOSE TableCursor;
DEALLOCATE TableCursor;