前言
有時候，數據庫的字段默認值沒有正確設置，這時候需要改默認值。以下是我做的改默認值的記錄，希望對網友有所幫助。
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
BEGINDEALLOCATE 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 = 'YourColumnName'; -- 替換為你的字段名OPEN TableCursor;FETCH NEXT FROM TableCursor INTO @TableName, @ColumnName;WHILE @@FETCH_STATUS = 0
BEGIN-- 獲取現有默認約束名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_idWHERE c.name = @ColumnName AND OBJECT_NAME(dc.parent_object_id) = @TableName;IF @ConstraintName IS NOT NULLBEGIN-- 刪除舊的默認約束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;
