数据库—修改某字段默认值
前言
有时候，数据库的字段默认值没有正确设置，这时候需要改默认值。以下是我做的改默认值的记录，希望对网友有所帮助。
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\') >= -1BEGIN DEALLOCATE TableCursor;END-- 游标遍历包含指定列的所有表DECLARE TableCursor CURSOR FORSELECT t.name AS TableName, c.name AS ColumnNameFROM sys.columns cINNER JOIN sys.tables t ON c.object_id = t.object_idWHERE c.name = \'YourColumnName\'; -- 替换为你的字段名OPEN TableCursor;FETCH NEXT FROM TableCursor INTO @TableName, @ColumnName;WHILE @@FETCH_STATUS = 0BEGIN -- 获取现有默认约束名 SELECT TOP 1 @ConstraintName = dc.name FROM sys.default_constraints dc INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id WHERE c.name = @ColumnName AND OBJECT_NAME(dc.parent_object_id) = @TableName; IF @ConstraintName IS NOT NULL BEGIN -- 删除旧的默认约束 SET @SqlCommand = \'ALTER TABLE [\' + @TableName + \'] DROP CONSTRAINT [\' + @ConstraintName + \']\'; EXEC sp_executesql @SqlCommand; END -- 添加新的默认值 SET @SqlCommand = \'ALTER TABLE [\' + @TableName + \'] ADD CONSTRAINT [DF_\' + @TableName + \'_\' + @ColumnName + \'] DEFAULT (NewDefaultValue) FOR [\' + @ColumnName + \']\'; -- 修改这里的默认值 EXEC sp_executesql @SqlCommand; FETCH NEXT FROM TableCursor INTO @TableName, @ColumnName;ENDCLOSE TableCursor;DEALLOCATE TableCursor;
