如何将默认为日期时间的小日期时间列更改为日期时间？

Question

我的数据库中有许多列最初创建为 smalldatetime，但实际上需要是 datetime。其中许多列是使用 DDL 创建的：

[columnname] smalldatetime not null default getdate()

...这意味着为了更改列的类型，我首先必须删除默认值，然后在更改后重新创建默认值。

当然，我没有在 DDL 中指定默认值的名称，因此所有列都有默认值，其名称类似于 DF__CaseLock__CaseCo__182C9B23。当我的应用程序创建其数据库时，它是通过执行脚本来完成的，因此我的客户数据库中的默认名称（我猜测；我尚未验证这一点）与我的数据库中的名称不同。

即使我知道默认约束的名称，我也无法弄清楚在删除它后将其添加回来的语法。事实上，我不清楚是否可以向现有列添加默认约束。

看来我必须做的是这样的：

declare @t table (id int not null primary key, date smalldatetime null)
insert into @t (id, date)
    select id, date_column from my_table
drop constraint constraint_name
alter table my_table drop column date_column;
alter table my_table add date_column datetime default getdate()
update my_table set date_column = t.date FROM my_table m JOIN @t t ON m.id = t.ID

...只有当我知道 constraint_name 是什么时，我才能编写该脚本，因为我无法删除约束中引用的列。

这真的有那么难吗？

Answer 1

您可以像这样添加默认约束：

ALTER TABLE TableName
ADD CONSTRAINT DF_DefaultName DEFAULT 'Default Value' FOR ColumnName

您可以询问 sys 表以查找现有约束。当您重新添加它们时，您应该为它们提供一个明确的名称。 这是一篇博客文章，其中有一个数字用于处理删除/添加未命名约束的脚本。希望有帮助！

Answer 2

恐怕你必须求助于动态 SQL。

但请记住，之前由旧默认设置设置的任何数据都将保留。

DECLARE @query varchar(256)

SET @query = (
    SELECT 'ALTER TABLE my_table DROP CONSTRAINT ' + d.name 
    FROM sysobjects d
        INNER JOIN sysobjects t ON t.id = d.parent_obj
    WHERE d.type = 'D'
        AND t.name = 'my_table')

EXEC(@query)

ALTER TABLE my_table
ADD CONSTRAINT DF_date_column default getdate() for date_column

编辑： my_table 中存在多个默认列：

CREATE TABLE #Defaults (
    strName varchar(256) PRIMARY KEY
)

INSERT INTO #Defaults (strName)
SELECT d.name 
FROM sysobjects d
    INNER JOIN sysobjects t ON t.id = d.parent_obj
WHERE d.type = 'D'
    AND t.name = 'my_table'

DECLARE @name varchar(256), @query varchar(256)

SET @name = (SELECT TOP 1 strName FROM #Defaults)

WHILE @name IS NOT NULL
BEGIN        
    SET @query = 'ALTER TABLE my_table DROP CONSTRAINT ' + @name

    EXEC(@query)

    DELETE FROM #Defaults 
    WHERE strName = @name

    SET @name = (SELECT TOP 1 strName FROM #Defaults)
END

ALTER TABLE my_table
ADD CONSTRAINT DF_date_column1 default getdate() for date_column1

ALTER TABLE my_table
ADD CONSTRAINT DF_date_column2 default getdate() for date_column2

DROP TABLE #Defaults