Alter All Columns datatype in SQL Server

Question

When I imported a table in SQL it suggested real datatype, now I would like to change all columns to double type...

Is there any script to automatically do this in SQL Managment Studio

My table is 500 columns:

`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

The following code will place a list of columns into a temporary table called @cols, loop through that table, generate an alter table alter column statement, and execute it for each column.

If you need to exclude columns, you should include those in the NOT IN predicate of the select from information_schema.columns.

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

Rough psuedocode would look like the following. It is untested however as I don't have a VM handy

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

Orange bar overhead says I'm too slow but I'll submit anyways as I spent far too much time typing ;)

Answer 3

Thanks to bilinkc!

That was the solution for me.

I made it work on MSSQL 2019 and added the is_nullable flag.

So with this script you can alter all columns in the named table and change the datatype including nullable and not nullable fields correctly.

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