SQL Server 2008：批量数据类型更改

Question

我有一个包含许多表的SQL Server 2008数据库。我一直在使用现在过时的datetime数据类型，想要使用新而更好的datetime2。在大多数情况下，我具有datetime字段的地方，相应的列名为Timestamp。是否有任何批量更改从datatime到datetime2的方法？

- Mel

---

将“buld”和“bul”更改为“bulk”，这样更容易理解 ;) - devio

Answer 1

在Management Studio中运行此命令，复制结果并粘贴到新的查询窗口中：

select 'ALTER TABLE ' + OBJECT_NAME(o.object_id) + 
    ' ALTER COLUMN ' + c.name + ' DATETIME2 ' +
    CASE WHEN c.is_nullable = 0 THEN 'NOT NULL' ELSE 'NULL' END 
from sys.objects o
inner join sys.columns c on o.object_id = c.object_id
inner join sys.types t on c.system_type_id = t.system_type_id
where o.type='U'
and c.name = 'Timestamp'
and t.name = 'datetime'
order by OBJECT_NAME(o.object_id)

- devio

---

我唯一想改变的是使用 sys.tables 而不是 sys.objects 来查找表名。 - marc_s

Answer 2

数据类型更改通常需要使用 ALTER TABLE 语句：

ALTER TABLE myTable ALTER COLUMN timestamp datetime2 [NOT] NULL

将给定数据库和模式中的所有日期时间列更改为datetime2：

DECLARE @SQL AS NVARCHAR(4000)
DECLARE @table_name AS NVARCHAR(255)
DECLARE @column_name AS NVARCHAR(255)
DECLARE @isnullable AS BIT

DECLARE CUR CURSOR FAST_FORWARD FOR
    SELECT c.table_name, 
           c.column_name, 
           CASE WHEN c.is_nullable = 'YES' THEN 1 ELSE 0 END AS is_nullable
      FROM INFORMATION_SCHEMA.COLUMNS c 
     WHERE c.data_type = 'datetime'
       AND c.table_catalog = 'your_database'
       AND c.table_schema = 'your_schema'
    -- AND c.table_name = 'your_table'

OPEN CUR
FETCH NEXT FROM CUR INTO @table_name, @column_name, @isnullable
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @SQL = 'ALTER TABLE ' + @table_name + ' ALTER COLUMN ' + @column_name + ' datetime2' + (CASE WHEN @isnullable = 1 THEN '' ELSE ' NOT' END) + ' NULL;'
    EXEC sp_executesql @SQL
    FETCH NEXT FROM CUR INTO @table_name, @column_name, @isnullable
END

CLOSE CUR;
DEALLOCATE CUR;

- OMG Ponies

---

很棒的答案，我投了赞成票。不过我会把这样的代码都包在一个事务里，以防出现问题。另外请注意，如果列上有约束条件（例如插入getdate()的默认值），上述脚本将失败。 - rmcsharry

但是您可以通过删除它们（http://stackoverflow.com/questions/4215619/how-to-drop-all-default-constraints-in-a-database），然后再添加它们来解决这个问题。 - rmcsharry

Answer 3

这可能有点蛮力方法，但你可以始终使用 sys.columns 视图查找所有数据类型为 datetime 的列，获取表名和列名，然后使用光标迭代该列表，并针对每条记录生成 ALTER TABLE 语句，如下所示：

ALTER TABLE @tablename ALTER COLUMN @columnname datetime2

然后使用EXEC运行该语句。显然，您需要有权限查询sys.columns和修改所有这些表的权限...

很抱歉这个回答中没有更多的代码——这台机器上没有SSMS的副本，也记不住所有的语法了。 :)

- Dan J

Answer 4

你可以像这样做：

SELECT
 'ALTER TABLE [' + Table_Schema+'].['+Table_Name
 +'] Alter Column ['+Column_Name+'] datetime2;'
 FROM
 INFORMATION_SCHEMA.COLUMNS
 WHERE DATA_TYPE='datetime';

现在您拥有了所有必要的脚本，可以进行批量类型更改。

参考：https://www.sqlservercentral.com/forums/topic/how-to-change-column-type-on-all-tables-of-a-certain-database

- kiafiore

Answer 5

我会使用查询窗口，并输出所有需要执行的 ALTER TABLE语句。一旦您生成了它们，就可以将结果运行到数据库中。

如果您从SYSCOLUMNS中选择要更改的表和字段的名称，则可以生成所需的语句，以将数据库中的所有列更改为datetime2。

ALTER TABLE {tablename} ALTER COLUMN {fieldname} datetime2 [NULL | NOT NULL]

- davisoa