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

Question

当我在 SQL 中导入表时，它建议使用真实数据类型，现在我想将所有列更改为双精度型...

是否有任何脚本可以在 SQL Managment Studio 中自动执行此操作

我的表有 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

Answer 1

以下代码将列列表放入名为 @cols 的临时表中，循环遍历该表，生成 alter table alter column 语句，并针对每列执行该语句。

如果需要排除列，则应将这些列包含在从 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

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

橙色条表示我速度太慢，但无论如何我都会提交，因为我花了太多时间打字；）

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.';