8

Is there a way to modify multiple columns in one sql query? For example I want to change the column definition of multiple columns in single table as follows. I am using SQL server 2012.

ALTER TABLE [Department]     
ALTER COLUMN [DepartmentName] VARCHAR(200) NULL ,
[ProjectManagerName] VARCHAR(200) NULL ,
[AccountManagerName] VARCHAR(200) NULL
SPKan
  • 555
  • 2
  • 12
  • 26
  • No, also there's no need to do this. If you want to do this all at the same time, then simple wrap all the alter statements in the same transaction. – Kahn May 11 '16 at 12:34

2 Answers2

17

Alter multiple columns in one time - impossible.

You could create Temp table with your edited columns, copy data from source table to temp table, drop your source table and rename temp table.

Stanislovas Kalašnikovas
  • 9,203
  • 4
  • 32
  • 59
  • It is possible in oracle : ALTER TABLE table_name ADD (column_1 column_definition, column_2 column_definition, ... column_n column_definition); – Visu Jan 08 '19 at 13:10
6

It is not possible to do multiple ALTER column for a table.

You have to alter them one by one like

ALTER TABLE Department ALTER COLUMN [DepartmentName] VARCHAR(200) NULL;
ALTER TABLE Department ALTER COLUMN [ProjectManagerName] VARCHAR(200) NULL;
ALTER TABLE Department ALTER COLUMN [ProjectManagerName] VARCHAR(200) NULL;
ALTER TABLE Department ALTER COLUMN [AccountManagerName] VARCHAR(200) NULL;
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331