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

小编典典

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

sql

我有一个数据库（Sql Server2005），其中有数十个数据库tables，每个数据库都有许多columns（平均10-20），数据类型设置为nvarchar(max)。这绝对是致命的性能（其中一些列用于其中joins，某些表具有100K+行）。我想将所有这些列更改为varchar(250)。自动执行此操作的最佳方法是什么？（我可以使用Management Studio，也可以创建一个实用程序来通过可ASP.net访问db的网站执行此操作，以较容易的为准）。

阅读 192

2021-05-05

共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。

享受！

2021-05-05