在SQL Server中修改所有列的数据类型

Question

当我在SQL中导入一个表时，它建议使用real数据类型，现在我想将所有列更改为double类型...

是否有脚本可以在SQL管理工作室中自动执行此操作？

我的表有500列：

`After doing` EXECUTE sp_help traS

Col Type  Comp len Prec Scale   Nullable TrimTrailing Fixed Collation
------------------------------------------------------------------------------- 
x1  real    no  4   24      NULL    yes (n/a)   (n/a)   NULL
x2  real    no  4   24      NULL    yes (n/a)   (n/a)   NULL
x3  real    no  4   24      NULL    yes (n/a)   (n/a)   NULL
x4  real    no  4   24      NULL    yes (n/a)   (n/a)   NULL
...
x500 real   no  4   24      NULL    yes (n/a)   (n/a)   NULL

- edgarmtze

---

嗯，光标应该怎么弄啊，不太明白... - edgarmtze

Answer 1

以下代码将会把一系列的列名放到一个名为@cols的临时表中，然后循环这个表，并为每一列生成一个alter table alter column语句，并执行。

如果你需要排除掉某些列，你应该在select from information_schema.columns中加入这些列名的NOT IN条件子句。

declare @cols table (i int identity, colname varchar(100))
insert into @cols
select column_name
from information_schema.COLUMNS
where TABLE_NAME = 'yourtable'
and COLUMN_NAME not in ('exclude1', 'exclude2')

declare @i int, @maxi int
select @i = 1, @maxi = MAX(i) from @cols

declare @sql nvarchar(max)

while(@i <= @maxi)
begin
    select @sql = 'alter table yourtable alter column ' + colname + ' decimal(18,4) NULL'
    from @cols
    where i = @i

    exec sp_executesql @sql

    select @i = @i + 1
end

- Derek Kromm

---

+1，但我建议操作者先访问此链接：http://www.sommarskog.se/dynamic_sql.html - Lamak

执行您的代码时，是否需要任何特权？我得到了以下消息：（影响了500行） Msg 156，级别15，状态1，第1行 关键字“NULL”附近的语法不正确。 Msg 156，级别15，状态1，第1行 关键字“NULL”附近的语法不正确。 Msg 156，级别15，状态1，第1行 关键字“NULL”附近的语法不正确。 Msg 156，级别15，状态1，第1行 关键字“NULL”附近的语法不正确。 Msg 156，级别15，状态1，第1行...... 关键字“NULL”附近的语法不正确。 Msg 156，级别15，状态1，第1行。 - edgarmtze

我也尝试了一个非动态的 alter table traS alter column x1 double null 但是出现了错误：INCORRECT SYNTAX NEAR NULL Expecting ID。 - edgarmtze

我更新了查询，请再试一次。Double不是有效的数据类型，你需要使用decimal或numeric代替。括号内的2个数字分别是总位数（精度）和小数点后出现的数字位数（标度）。 - Derek Kromm

@Derek Kromm：一个问题，我该如何将列的数据类型设置为double（猜测有一个选项是double）...并不是说decimal没用，只是想设置成double类型。 - edgarmtze

@cMinor，这不是.NET或Java - 没有双精度类型。您的选项是数字和十进制，据我所知，它们是等效的。最大数字是10^33 - 1。如果您需要保存更大的数字，则需要使用float。有关数据类型的更多信息，请参见此处：http://msdn.microsoft.com/en-us/library/ms187752.aspx - Derek Kromm

Answer 2

粗略的伪代码如下所示。但由于我没有可用的虚拟机，因此未经测试。

-- Create a cursor that will iterate through
-- all the rows that meet the criteria DECLARE csr CURSOR FOR 
-- This query attempts to define the set of columns
-- that are reals  
SELECT 
    SC.name AS column_name  
FROM
    sys.tables ST 
    INNER JOIN 
        sys.columns SC
        ON SC.object_id = ST.object_id
    INNER JOIN
        sys.types T
        -- these column names are close but not right
        ON T.type_id = SC.system_type_id 
WHERE
    -- make this your table name
    ST.name = 'traS'
    -- look at actual values in sys.types
    AND T.name = 'real'

DECLARE 
    -- this holds the current column name
    @column_name sysname 
,   @base_query varchar(max) 
,   @actual_query varchar(max)

-- template query for fixing what's buggered
SET @base_query = 'ALTER TABLE traS ALTER COLUMN [<X/>] decimal(18,2) NULL'

FETCH NEXT FROM csr 
INTO @column_name  
WHILE (@@FETCH_STATUS <> -1) BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
        BEGIN TRY
            SET @actual_query = REPLACE(@base_query, '<X/>', @column_name)
            EXECUTE (@actual_query)
        END TRY
        BEGIN CATCH
            PRINT 'Failed executing statement '
            PRINT @actual_query
        END CATCH
    END
    FETCH NEXT FROM csr 
    INTO @colum_name 
END 
CLOSE csr 
DEALLOCATE csr

橙色的顶部栏显示我太慢了，但我还是会提交，因为我花了太多时间打字 ;)

- billinkc

---

一个问题，你怎么得到 C.name？ - edgarmtze

查询应该是SC，因为sys.column表将保存列的名称。已修复该查询。 - billinkc

此外，您可能需要阅读此线程并确定您的应用程序需要哪种数据类型https://dev59.com/1HM_5IYBdhLWcg3w1G2N - billinkc

Answer 3

感谢bilinkc！

那对我来说就是解决方案。

我让它在 MSSQL 2019 上运行，并添加了 is_nullable 标志。

因此，使用此脚本，您可以正确地更改命名表中的所有列并更改数据类型，包括可空和非可空字段。

declare @TableName varchar(255) = 'TableName';
declare @DataTypeOld varchar(max) = 'float';
declare @DataTypeNew varchar(max) = 'decimal (19,4)';

DECLARE csrTemp CURSOR FOR
SELECT 
    SC.name AS column_name, SC.is_nullable
FROM
    sys.tables ST 
    INNER JOIN 
        sys.columns SC
        ON SC.object_id = ST.object_id
    INNER JOIN
        sys.types T
        ON T.system_type_id = SC.system_type_id
WHERE
    ST.name = @TableName
    AND T.name = @DataTypeOld
OPEN csrTemp;

DECLARE 
    -- this holds the current column name
    @column_name sysname,
    @is_nullable bit,
    @base_query varchar(max),
    @actual_query varchar(max);

-- template query for changing the datatype
SET @base_query = 'ALTER TABLE '+@TableName+' ALTER COLUMN [<X/>] '+ @DataTypeNew;

declare @Count int = 0;
FETCH NEXT FROM csrTemp 
INTO @column_name, @is_nullable;
WHILE (@@FETCH_STATUS <> -1) BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
        SET @Count = @Count +1;
        BEGIN TRY
            SET @actual_query = REPLACE(@base_query, '<X/>', @column_name);
            IF @is_nullable = 1
                SET @actual_query = @actual_query + ' NULL';
            ELSE
                SET @actual_query = @actual_query + ' NOT NULL';
            EXECUTE (@actual_query);
            PRINT @actual_query;
        END TRY
        BEGIN CATCH
            SET @Count = @Count -1;
            PRINT '---------------------------';
            PRINT 'Failed executing statement: '+@actual_query;
            PRINT 'ERROR: '+ ERROR_MESSAGE();
            PRINT '';
        END CATCH
    END
    FETCH NEXT FROM csrTemp
    INTO @column_name, @is_nullable;
END 
CLOSE csrTemp;
DEALLOCATE csrTemp;
PRINT '---------------------------';
print 'Altered '+ cast(@Count as varchar) + ' columns.';

- this.harry