SQL Server 2008:批量数据类型更改 [英] SQL Server 2008: Bulk Datatype Change

问题描述

我有一个包含许多表的 SQL Server 2008 数据库.我一直在使用现在蹩脚的 datetime 数据类型，并希望使用新的更好的 datetime2.在我有日期时间字段的大多数地方，对应的列名称是 Timestamp.是否有任何地方可以从 datatime 到 datetime2 进行批量更改?

I have an SQL Server 2008 database with many tables. I've been using the now lame datetime datatype and want to use the new and better datetime2. In most places where I have a datetime field, the corresponding column name is Timestamp. Is there anywhere to do a bulk change from datatime to datetime2?

推荐答案

在 Management Studio 中运行此程序，将结果复制并粘贴到新的查询窗口中:

Run this in Management Studio, copy the result and paste into new Query Window:

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)

这篇关于SQL Server 2008:批量数据类型更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！