How to ALTER multiple columns at once in SQL Server

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

For a single column, the following works fine:

  • How to automatically close SQL Server connections from IIS for DB rebuild in Integration Test
  • How do I enable MSDTC on SQL Server?
  • Concise/elegant method for duplicating a table's schema?
  • Cannot edit subscriptions after renaming Reporting Server
  • SQL view with dynamic count of columns
  • Determining the Level of Hierarchy in SQL
  • 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)
    

    12 Solutions collect form web for “How to ALTER multiple columns at once in SQL Server”

    This is not possible. You will need to do this one by one.

    You could create a Temporary Table with your modified columns in, copy the data across, drop your original table and rename your Temporary Table to your original name.

    Doing multiple ALTER COLUMN actions inside a single ALTER TABLE statement is not
    possible.

    See the ALTER TABLE syntax here:
    http://msdn.microsoft.com/en-US/library/ms190273.aspx

    You can do multiple ADD or multiple DROP COLUMN, but just one ALTER
    COLUMN.

    The following solution is not a single statement for altering multiple columns, but yes, it makes life simple:

    1. Generate a table’s CREATE script.

    2. Replace CREATE TABLE with ALTER TABLE [TableName] ALTER COLUMN for first line

    3. Remove unwanted columns from list.

    4. Change the columns data types as you want.

    5. Perform a Find and Replace… as follows:

      1. Find: NULL,
      2. Replace with: NULL; ALTER TABLE [TableName] ALTER COLUMN
      3. Hit Replace button.
    6. Run the script.

    Hope it will save lot of time :))

    As others have answered, you need multiple ALTER TABLE statements. Try:

    ALTER TABLE tblcommodityOHLC alter column CC_CommodityContractID NUMERIC(18,0);
    ALTER TABLE tblcommodityOHLC alter column CM_CommodityID NUMERIC(18,0);
    

    etc.

    If you do the changes in management studio and generate scripts it makes a new table and inserts the old data into that with the changed data types. Here is a small example changing two column’s data types

    /*
       12 August 201008:30:39
       User: 
       Server: CLPPRGRTEL01\TELSQLEXPRESS
       Database: Tracker_3
       Application: 
    */
    
    /* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
    BEGIN TRANSACTION
    SET QUOTED_IDENTIFIER ON
    SET ARITHABORT ON
    SET NUMERIC_ROUNDABORT OFF
    SET CONCAT_NULL_YIELDS_NULL ON
    SET ANSI_NULLS ON
    SET ANSI_PADDING ON
    SET ANSI_WARNINGS ON
    COMMIT
    BEGIN TRANSACTION
    GO
    ALTER TABLE dbo.tblDiary
        DROP CONSTRAINT FK_tblDiary_tblDiary_events
    GO
    ALTER TABLE dbo.tblDiary_events SET (LOCK_ESCALATION = TABLE)
    GO
    COMMIT
    BEGIN TRANSACTION
    GO
    CREATE TABLE dbo.Tmp_tblDiary
        (
        Diary_ID int NOT NULL IDENTITY (1, 1),
        Date date NOT NULL,
        Diary_event_type_ID int NOT NULL,
        Notes varchar(MAX) NULL,
        Expected_call_volumes real NULL,
        Expected_duration real NULL,
        Skill_affected smallint NULL
        )  ON T3_Data_2
         TEXTIMAGE_ON T3_Data_2
    GO
    ALTER TABLE dbo.Tmp_tblDiary SET (LOCK_ESCALATION = TABLE)
    GO
    SET IDENTITY_INSERT dbo.Tmp_tblDiary ON
    GO
    IF EXISTS(SELECT * FROM dbo.tblDiary)
         EXEC('INSERT INTO dbo.Tmp_tblDiary (Diary_ID, Date, Diary_event_type_ID, Notes, Expected_call_volumes, Expected_duration, Skill_affected)
            SELECT Diary_ID, Date, Diary_event_type_ID, CONVERT(varchar(MAX), Notes), Expected_call_volumes, Expected_duration, CONVERT(smallint, Skill_affected) FROM dbo.tblDiary WITH (HOLDLOCK TABLOCKX)')
    GO
    SET IDENTITY_INSERT dbo.Tmp_tblDiary OFF
    GO
    DROP TABLE dbo.tblDiary
    GO
    EXECUTE sp_rename N'dbo.Tmp_tblDiary', N'tblDiary', 'OBJECT' 
    GO
    ALTER TABLE dbo.tblDiary ADD CONSTRAINT
        PK_tblDiary PRIMARY KEY NONCLUSTERED 
        (
        Diary_ID
        ) WITH( PAD_INDEX = OFF, FILLFACTOR = 86, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON T3_Data_2
    
    GO
    CREATE UNIQUE CLUSTERED INDEX tblDiary_ID ON dbo.tblDiary
        (
        Diary_ID
        ) WITH( PAD_INDEX = OFF, FILLFACTOR = 86, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON T3_Data_2
    GO
    CREATE NONCLUSTERED INDEX tblDiary_date ON dbo.tblDiary
        (
        Date
        ) WITH( PAD_INDEX = OFF, FILLFACTOR = 86, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON T3_Data_2
    GO
    ALTER TABLE dbo.tblDiary WITH NOCHECK ADD CONSTRAINT
        FK_tblDiary_tblDiary_events FOREIGN KEY
        (
        Diary_event_type_ID
        ) REFERENCES dbo.tblDiary_events
        (
        Diary_event_ID
        ) ON UPDATE  CASCADE 
         ON DELETE  CASCADE 
    
    GO
    COMMIT
    

    As lots of others have said, you will need to use multiple ALTER COLUMN statements, one for each column you want to modify.

    If you want to modify all or several of the columns in your table to the same datatype (such as expanding a VARCHAR field from 50 to 100 chars), you can generate all the statements automatically using the query below. This technique is also useful if you want to replace the same character in multiple fields (such as removing \t from all columns).

    SELECT
         TABLE_CATALOG
        ,TABLE_SCHEMA
        ,TABLE_NAME
        ,COLUMN_NAME
        ,'ALTER TABLE ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] ALTER COLUMN ['+COLUMN_NAME+'] VARCHAR(300)' as 'code'
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'your_table' AND TABLE_SCHEMA = 'your_schema'
    

    This generates an ALTER TABLE statement for each column for you.

    select 'ALTER TABLE ' + OBJECT_NAME(o.object_id) + 
        ' ALTER COLUMN ' + c.name + ' DATETIME2 ' + 
        CASE WHEN c.is_nullable = 0 THEN 'NOT NULL' ELSE 'NULL' END
    from sys.objects o
    inner join sys.columns c on o.object_id = c.object_id
    inner join sys.types t on c.system_type_id = t.system_type_id
    where o.type='U'
    and c.name = 'Timestamp'
    and t.name = 'datetime'
    order by OBJECT_NAME(o.object_id)
    

    courtesy of devio

    We can alter multiple columns in single query like this
    ALTER TABLE tblcommodityOHLC
    CHANGE COLUMN updated_on updated_on DATETIME NULL DEFAULT NULL AFTER updated_by,
    CHANGE COLUMN delivery_datetime delivery_datetime DATETIME NULL DEFAULT CURRENT_TIMESTAMP AFTER delivery_status;

    give the queries as comma separated

    If i understood your question correctly you can add multiple columns in a table by using below mentioned query.

    Query:

    Alter table tablename add (column1 dataype, column2 datatype);
    

    — create temp table
    CREATE TABLE temp_table_alter
    (
    column_name varchar(255)
    );

    — insert those coulmns in temp table for which we nee to alter size of columns

    INSERT INTO temp_table_alter (column_name) VALUES ('colm1');
    INSERT INTO temp_table_alter (column_name) VALUES ('colm2');
    INSERT INTO temp_table_alter (column_name) VALUES ('colm3');
    INSERT INTO temp_table_alter (column_name) VALUES ('colm4');
    
    DECLARE @col_name_var varchar(255);
    DECLARE alter_table_cursor CURSOR FOR
    select column_name from temp_table_alter ;
    
    OPEN alter_table_cursor
    FETCH NEXT FROM alter_table_cursor INTO @col_name_var
    WHILE @@FETCH_STATUS = 0
     BEGIN
    
     PRINT('ALTER COLUMN ' + @col_name_var);
     EXEC ('ALTER TABLE Original-table  ALTER COLUMN ['+ @col_name_var + '] DECIMAL(11,2);')
    
     FETCH NEXT FROM alter_table_cursor INTO @col_name_var
     END
    
    CLOSE alter_table_cursor
    

    DEALLOCATE alter_table_cursor

    — at the end drop temp table
    drop table temp_table_alter;

    We can do it. Just you have to use the keyword modify before every column you want to alter.

    For example: I have 3 columns A, B, C in table XYZ and I need to change the data-type from varchar(255) to text. Then I need to write as:

    alter table XYZ modify A text, modify B text, modify C text;
    

    Put ALTER COLUMN statement inside a bracket, it should work.

    ALTER TABLE tblcommodityOHLC alter ( column  
    CC_CommodityContractID NUMERIC(18,0), 
    CM_CommodityID NUMERIC(18,0) )
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.