如何将一种数据类型的所有 Sql 列更改为另一种 [英] How to Change All Sql Columns of One DataType into Another

问题描述

我有一个数据库(Sql Server 2005)，其中有几十个表，每个表都有许多列(在平均 10-20) 数据类型设置为 nvarchar(max).这绝对会降低性能(其中一些列用于 joins 并且一些表有 100K+ 行).我想将所有这些列更改为 varchar(250).自动化此操作的最佳方法是什么?(我可以使用 Management Studio，或者我可以创建一个实用程序来通过可以访问数据库的 ASP.net 网站来执行此操作，以更容易的为准.>

I have a database (Sql Server 2005) where there are dozens of tables, each of which has a number of columns (on average 10-20) with datatype set to nvarchar(max). This is absolutely killing performance (some of these columns are being used for joins and some of the tables have 100K+ rows). I would like to change all of these columns to be varchar(250). What would be the best way to automate this? (I could use Management Studio, or I could create a utility to perform this through an ASP.net website that has access to the db, whichever is easier).

推荐答案

这是一个使用 INFORMATION_SCHEMA.COLUMNS 查找所有 *varchar(max) 的工作脚本列并将它们转换为 varchar(255):

Here's a working script that uses INFORMATION_SCHEMA.COLUMNS to find all of the *varchar(max) columns and converts them to 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 执行它.

This is about the only use of cursors that I ever condone, but it's a good one. Essentially, it finds all of the *varchar(max), builds the alter statement, and then executes it using sp_executesql.

享受吧！

这篇关于如何将一种数据类型的所有 Sql 列更改为另一种的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！