How to ALTER multiple columns at once in SQL Server

Question

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

User · Answer

We can alter multiple columns in a 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`;

Just give the queries as comma separated.

User · Answer

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

User · Answer

As others have answered  you need multiple ALTER TABLE statements  Try following   ALTER TABLE tblcommodityOHLC alter column CC CommodityContractID NUMERIC 18 0   ALTER TABLE tblcommodityOHLC alter column CM CommodityID NUMERIC 18 0

User · Answer

Doing multiple ALTER COLUMN actions inside a single ALTER TABLE statement is not possible   See the ALTER TABLE syntax here  You can do multiple ADD or multiple DROP COLUMN  but just one ALTER COLUMN

User · Answer

Thanks to Evan s code sample  I was able to modify it more and get it more specific to tables starting with  specific column names AND handle specifics for constraints too   I ran that code and then copied the  CODE  column and executed it without issue   USE  Table Name  GO SELECT      TABLE CATALOG      TABLE SCHEMA      TABLE NAME      COLUMN NAME      DATA TYPE       ALTER TABLE    TABLE SCHEMA       TABLE NAME    DROP CONSTRAINT  DEFAULT   TABLE NAME     COLUMN NAME      ALTER TABLE     TABLE SCHEMA       TABLE NAME    ALTER COLUMN    COLUMN NAME    datetime2  7  NOT NULL  ALTER TABLE     TABLE SCHEMA       TABLE NAME    ADD CONSTRAINT  DEFAULT   TABLE NAME     COLUMN NAME    DEFAULT    3 6 2018 6 47 23 PM    FOR    COLUMN NAME      GO  AS   CODE   FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME LIKE  form    AND TABLE SCHEMA    dbo   AND  COLUMN NAME    FormInserted  OR COLUMN NAME    FormUpdated    AND DATA TYPE    datetime

User · Answer

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  Double check before   Rename your Temporary Table to your original name

User · Answer

The following solution is not a single statement for altering multiple columns  but yes  it makes life simple    Generate a table s CREATE script   Replace CREATE TABLE with ALTER TABLE  TableName  ALTER COLUMN for first line    Remove unwanted columns from list  Change the columns data types as you want  Perform a Find and Replace    as follows    Find  NULL  Replace with  NULL  ALTER TABLE  TableName  ALTER COLUMN Hit Replace button   Run the script    Hope it will save lot of time

User · Answer

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

User · Answer

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

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] How to ALTER multiple columns at once in SQL Server

Examples related to sql-server

Examples related to tsql

Examples related to alter-column