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)

@onedaywhen - So that SQL Server would just make one pass through the table to do any necessary validation against the new datatype and/or writing out the altered columns in the new format. — Martin Smith, Apr 19 '15 at 09:53
Contrary. It would be a great advantage to have an alter run in 2 hrs instead of 24 for multiple columns on big tables. — Norbert Kardos, Feb 27 '18 at 15:32
Perceived Benefit: Less Copy&Paste maintenance when, for instance, modifying the script to run on the same table in a different database. — RoboticRenaissance, Oct 08 '21 at 20:58

score 183 · Answer 1 · edited Aug 04 '20 at 09:58

183

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

edited Aug 04 '20 at 09:58

hdoghmen

3,175
4
29
33

answered Aug 12 '10 at 07:30

Neil Knight

47,437
25
129
188

7

+1, `You will need to do this one by one.`, so, whats the big deal, just use multiple `ALTER TABLE ALTER COLUMN` commands? – KM. Aug 12 '10 at 12:56
13

@KM One problem is if you are altering a big table. Each statement means a new scan but if you could alter multiple columns, all altering could have been much quicker – erikkallen Sep 08 '16 at 06:31
@erikkallen, then do like SSMS tools usually generate their scripts: create a new table and replicate FKs and indexes, etc, drop original table and then rename new table, – KM. Sep 08 '16 at 12:24
1

Dropping and recreating tables is a pretty intensive operation. It's disabled by default in SSMS now, and probably for good reason. – jocull Nov 10 '17 at 19:29

score 57 · Answer 2 · edited Oct 01 '19 at 05:50

57

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.

edited Oct 01 '19 at 05:50

Muhammad Waheed

1,048
1
13
30

answered Jul 05 '13 at 03:54

Fernando Torres

1,070
8
19

score 42 · Answer 3 · edited Oct 01 '19 at 06:31

42

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

edited Oct 01 '19 at 06:31

Muhammad Waheed

1,048
1
13
30

answered Aug 10 '16 at 21:02

Ray K.

2,431
3
25
39

Using copy& paste + window function under SSMS or even Notepad++, you can list your columns and new types or constraints, and prepend the ALTER TABLE x ALTER COLUMN to each column that needs modification. Looks kitsch, but so was having such an out of whack table specification in the first place. – alejandrob Oct 04 '22 at 18:47

score 14 · Answer 4 · edited Jan 13 '17 at 10:30

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:
1. Find: NULL,
2. Replace with: NULL; ALTER TABLE [TableName] ALTER COLUMN
3. Hit Replace button.
Run the script.

Hope it will save lot of time :))

score 10 · Answer 5 · answered Jul 26 '17 at 15:32

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.

score 2 · Answer 6 · answered Jan 25 '19 at 18:53

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

score 1 · Answer 7 · answered Aug 12 '10 at 07:33

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

score 0 · Answer 8 · edited May 23 '17 at 12:02

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

score 0 · Answer 9 · edited Nov 29 '18 at 16:30

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

NOT a good solution. Cursors and loops should be avoided at ALL costs!!! — Ray K., Aug 06 '16 at 15:44
Not true, Ray. Cursors and loops are fine for certain DDL tasks and other necessarily RBR tasks. — Jeff Moden, Jun 14 '17 at 17:27

score 0 · Answer 10 · answered Apr 02 '18 at 17:36

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'

score -3 · Answer 11 · edited Jan 02 '15 at 09:34

-3

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

edited Jan 02 '15 at 09:34

mirabilos

5,123
2
46
72

answered Jan 02 '15 at 09:06

Jai Prakash Rai

17

score -3 · Answer 12 · edited Jan 31 '18 at 03:22

-3

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.

edited Jan 31 '18 at 03:22

Pang

9,564
146
81
122

answered May 08 '17 at 06:25

Muhammed Shihabudeen Labba A

587
6
21

4

I think this is MySql? The question was for SQL Server, and this doesn't work in sql server – Tjipke Jul 26 '18 at 11:51

score -3 · Answer 13 · answered Jul 14 '17 at 10:36

-3

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

answered Jul 14 '17 at 10:36

Virendra Gawade

118
1
8

4

The OP asked about ALTER column, not ADD. – D.R. Feb 06 '18 at 20:32

How to ALTER multiple columns at once in SQL Server

13 Answers13

Linked

Related