How to ALTER multiple columns at once in SQL Server

user_418105 0
Viewed 398267

I need to ALTER the data types of several columns in a table.

For a single column, the following works fine:

ALTER TABLE tblcommodityOHLC
ALTER COLUMN
    CC_CommodityContractID NUMERIC(18,0)

But how do I alter multiple columns in one statement? The following does not work:

ALTER TABLE tblcommodityOHLC
ALTER COLUMN
    CC_CommodityContractID NUMERIC(18,0), 
    CM_CommodityID NUMERIC(18,0)
alter-column tsql sql-server
13 Answers

If you don't want to write the whole thing yourself and change all the columns to the same datatype this can make it easier:

select 'alter table tblcommodityOHLC alter column '+name+ 'NUMERIC(18,0);'
from syscolumns where id = object_id('tblcommodityOHLC ')

You can copy and paste the output as your query

user_75865440
Related