如何将所有数据类型为某一种的Sql列更改为另一种数据类型

Question

我有一个数据库（Sql Server 2005），其中有数十个表，每个表都有若干列（平均每个表有10-20列），数据类型设置为nvarchar(max)。这绝对会影响性能（其中一些列用于join，而一些表具有100K+行）。我想将所有这些列都更改为varchar(250)。最好的自动化方法是什么？（我可以使用Management Studio，或者我可以创建一个实用程序通过具有对数据库的访问权限的ASP.net网站执行此操作，以便更容易完成）。

- Yaakov Ellis

Answer 1

以下是一份可用的脚本，它使用INFORMATION_SCHEMA.COLUMNS查找所有*varchar(max)列，并将它们转换为varchar(255):

declare @schema nvarchar(255)
declare @table nvarchar(255)
declare @col nvarchar(255)
declare @dtype nvarchar(255)
declare @sql nvarchar(max)

declare maxcols cursor for
select
    c.TABLE_SCHEMA,
    c.TABLE_NAME,
    c.COLUMN_NAME,
    c.DATA_TYPE
from
INFORMATION_SCHEMA.COLUMNS c
inner join INFORMATION_SCHEMA.TABLES t on
    c.TABLE_CATALOG = t.TABLE_CATALOG
    and c.TABLE_SCHEMA = t.TABLE_SCHEMA
    and c.TABLE_NAME = t.TABLE_NAME
    and t.TABLE_TYPE = 'BASE TABLE'
where
    c.DATA_TYPE like '%varchar'
    and c.CHARACTER_MAXIMUM_LENGTH = -1

open maxcols

fetch next from maxcols into @schema, @table, @col, @dtype

while @@FETCH_STATUS = 0
begin
    set @sql = 'alter table [' + @schema + '].[' + @table + 
        '] alter column [' + @col + '] ' + @dtype + '(255)'
    exec sp_executesql @sql

    fetch next from maxcols into @schema, @table, @col, @dtype
end

close maxcols
deallocate maxcols

这是我唯一支持使用光标的情况，但这是个好方法。本质上，它会查找所有的*varchar(max)，构建alter语句，然后使用sp_executesql执行它。

享受吧！

- Eric

---

这并不完美，因为它还会将视图中的列带入。 - Yaakov Ellis

现在，我们该如何删除约束并在此过程中将其放回，因为我们无法更改已存在约束的列？ - JosephK

是否考虑添加一个检查，仅适用于最大长度小于255的列，以避免潜在的数据截断？ - windowsgm

Answer 2

您可以轻松地使用以下方法找到它们：

select 'alter table ' + quotename(o.name) + ' alter column ' + quotename(c.name) + ' varchar(250); '
from sys.columns c
  join
  sys.objects o
  on o.object_id = c.object_id
where o.type = 'U'
and c.user_type_id = 231
and c.max_length = -1

现在，只需获取查询结果并运行即可。

Rob

- Rob Farley

---

也许值得一提的是，如果您放置declare @qry nvarchar(max)，并选择@qry = (.... for xml path('')); exec sp_executesql @qry; --您可以在一个步骤中完成所有操作。这是我目前的查询。但是，您就不能很好地进行“合理性检查”了。 - Rob Farley

您需要考虑列的可空性吗？这会改变列的可空性，因为它没有明确指定。 - Phil Hale

Answer 3

我认为完成这个任务的最佳方式是生成当前数据库的脚本，然后在文本文件中将nvarchar(max)替换为varchar(250)，然后创建新的数据库。之后使用导入/导出工具将数据转移到新数据库。

- Wael Dalloul

---

这个不行 - SSIS会返回一个错误信息，指出源表和目标表之间的列元数据不匹配。 - Yaakov Ellis