Posts
1150
Comments
891
Trackbacks
1
T-SQL: Can you alter multiple columns within a single alter table statement?

No.  You can add multiple columns, but you have to use a separate alter table statement for each column you want to alter.

posted on Monday, May 09, 2005 1:55 PM Print
Comments
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
x002548
5/6/2008 12:30 PM
Are you smoking something?

http://weblogs.sqlteam.com/brettk/category/72.aspx
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
jdn
5/6/2008 1:08 PM
Sorry, but your link has to do with updating multiple tables within an update statement.

I'm talking about altering multiple columns within a single alter table statement.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Major Singh
5/22/2008 3:51 AM
Yes, You can Add multiple columns with s single alter statement. The statement will be..
ALTER TABLE TABLENAME
ADD TEST1 varchar(100) NULL,
TEST2 varchar(100) NULL,
TEST3 varchar(100) NULL

Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
jdn
5/22/2008 6:23 PM
No, again, I'm talking about altering multiple columns within a single alter statement.

You are talking about adding them. Different thing.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Dercsár
8/26/2008 4:09 AM
jdn is right. You can not modify multiple columns in one statement, which is weird imo.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Carl
5/20/2009 10:27 AM
ALTER TABLE t ALTER COLUMN c1 int null;
ALTER TABLE t ALTER COLUMN c2 int null;
ALTER TABLE t ALTER COLUMN c3 int null;
ALTER TABLE t ALTER COLUMN c4 int null;
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
jdn
5/20/2009 11:19 PM
@Carl

That's four alter table statements, as I indicated.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Darrick
10/8/2009 11:51 PM
Just came across this post... and I just wonder how the 3 suggested answers never bothered to read the original statement and kept suggesting the same thing as the previous poster :)
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Tilt
10/23/2009 7:21 AM
@Darrick

You, Sir, totally failed at misreading the Question. Instead of making sane and true statements you could have kept these comments a fun read by suggesting a query that updates multiple values in single column. But noooo...
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
ram
3/19/2011 3:39 PM
Thanks
Your help appreciated
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
ASPNETER
4/17/2011 1:41 AM
it is not possible to use Alter Table statement for altering multiple columns. Personally i do not accept those silly explanation about this serious limitation of SQL Server.
Therefor i consider this as a bug!
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
GiBi
6/21/2011 4:00 AM
This is funny; I had a problem with adding 2 columns to a table, I misread the title and kept reading, and I found my answer in Major Singh's post.
By the way, I was writing a similar statement WITH parenthesis enclosing the list of fields; I took them out, as in the above example, and it worked.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Rob
8/9/2011 6:16 PM
I too found my answer in Major Singh's post. For some reason this post came up top in Google when I was trying to find out why my t-sql syntax was not quite right for adding multiple columns to an already existing table using a single alter statement.

BTW - Tilt's response to Darrick is priceless. :)
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Mike
8/15/2011 10:29 AM
Thanks !
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Ramu
11/25/2011 1:33 AM
Thanq

Yes, I Can you alter multiple columns within a single alter table statement.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
jdn
11/26/2011 10:13 PM
@Ramu

No, you can't
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Ephraim Kerr
1/12/2012 8:39 AM
Nothing is impossible. Yes you can. Unfortunately you would need to create a cursor from metadata and write an alter statement for each column.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
jdn
1/13/2012 10:13 AM
@Ephraim

With only a single alter table statement? I don't think so.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Prestonean
3/26/2012 7:13 PM
But - you can use excel, list the column names, and do a simple concat formula to make as many ALTER statements as you need very quickly...
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
James
3/27/2012 11:18 AM
If you have the correct permissions you might be able to do an update statement on the columns table in the information_schema schemata instead.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
James
3/27/2012 11:20 AM
Oh I see, this is MSSQL, lol. Don't you like money?
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
RIck
5/17/2012 2:37 AM
@James

Some of us work for a living
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
teikercillu
6/15/2012 8:46 AM
yes you can >.<

ALTER TABLE t ALTER COLUMN col1 int null;
ALTER TABLE t ALTER COLUMN col2 int null;
ALTER TABLE t ALTER COLUMN col3 int null;
ALTER TABLE t ALTER COLUMN col4 int null;
ALTER TABLE t ALTER COLUMN col5 int null;
ALTER TABLE t ALTER COLUMN col6 int null;
ALTER TABLE t ALTER COLUMN col7 int null;
ALTER TABLE t ALTER COLUMN col8 int null;
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
jdn
6/29/2012 10:33 AM
Those are multiple statements. Read the title.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Leon
8/22/2012 10:35 AM
I think I just lost all faith in humanity.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
ks
9/18/2012 6:30 AM
lol. I think everyone is smoking something. very funny.
I was looking for a confirmation that we indeed cannot alter multiple columns within a single alter table and I got my laugh of the day.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Not Relevant
9/26/2012 3:03 AM
exec (N'ALTER TABLE t ALTER COLUMN col1 int null; ALTER TABLE t ALTER COLUMN col2 int null; ALTER TABLE t ALTER COLUMN col3 int null; ALTER TABLE t ALTER COLUMN col4 int null; ALTER TABLE t ALTER COLUMN col5 int null; ALTER TABLE t ALTER COLUMN col6 int null; ALTER TABLE t ALTER COLUMN col7 int null; ALTER TABLE t ALTER COLUMN col8 int null;')
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
jdn
9/26/2012 8:47 AM
@Not Relevant:

You are clearly not relevant, as that is EIGHT alter table statements.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Chris
9/26/2012 8:48 AM
I've enjoyed reading this thanks guys. :) and I learned something.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Not Relevant
9/26/2012 9:19 AM
It's clearly _one_ statement with a string argument!
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
jdn
9/26/2012 9:42 AM
@Not relevant:

No, it isn't. It is eight statements, each delimited by a semi-colon, passed in as a single parameter.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
SDC
9/28/2012 2:44 PM
OK, I hate people now. Nobody read the damn question.
Gravatar
# Mr
Don Jacob
10/26/2012 11:42 AM
@jdn - Cannot be done. Now get back to work.<br>
To all the wannabees on the thread who posted claiming to know something - do the same and read the requirements carefully before you start coding.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Rast
10/30/2012 11:50 AM
The first answer in this thread was the right one.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
safd
11/8/2012 1:52 PM
thread has first response in 2005. Then few in 2008, 2009. I dont know what happened in 2010, no responses :( sense of humor must have gone down in 2010. Then again we have replies in 2011 and 2012.

Thanks everyone, had a good laugh reading this. So to keep the thread alive I'm adding my post tooo..
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
curt
11/14/2012 9:27 AM
Bahaha, xD.

DECLARE @prefix AS VARCHAR(50)
DECLARE @suffix1 AS VARCHAR(50)
DECLARE @suffix2 AS VARCHAR(50)

SET @prefix = 'ALTER TABLE t'
SET @suffix1 = ' ALTER COLUMN col1 int null'
SET @suffix2 = ' ALTER COLUMN col1 int null'

exec(@prefix + @suffix1 + ';' + @prefix + @suffix2 + ';')
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
jdn
11/14/2012 10:37 AM
@curt

That is two alter table statements. The use of one variable doesn't change that.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Neha
12/5/2012 4:01 AM
lolz...while reading this block I found my answer to my question, also kept laughing after reading some of the question-answers debate :)
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
anon
12/17/2012 10:41 AM
This entire comment thread needs to be nuked from orbit.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Craig
1/29/2013 7:46 AM
Omg, I don't even. Did you guys come over from yahoo answers or something?
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
ander
2/12/2013 6:45 PM
Googled here for the ALTER TABLE info, but left with so much more. Thanks for the lulz. I really needed this today. This thread is such win!
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Andrew
2/28/2013 2:55 AM
This really made my day :) Been sitting in a meeting room all day and needed help altering multiple columns in one "ALTER TABLE <table> atler/modify COLUMN col1 , col2, col3". Got a few looks from those around me when I couldn't hold back a little laugh :)
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Ed
3/15/2013 10:23 AM
Something like this:

ALTER TABLE [Table]
Alter COLUMN [Column] TO [Column] VARCHAR(50) not null;

Yeah sorry to get your hopes up but that just there^ doesn't work. Would be nice... and yes a read the question.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
jdn
3/15/2013 10:38 AM
@Ed:

Huh?
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
JoZie
4/5/2013 1:47 PM
OMG. So many FAILS in one thread. I couldn't stop laughing. Thanks folks. Keep up the great work. @JDN, you are the funniest douchebag on the planet, but you are still a douchebag. LOL
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
jdn
4/7/2013 3:04 PM
@JoZie

I aim to please.
Gravatar
# how to change one column in more than tables
suresh
8/10/2013 1:55 AM
how to change one column details in more than tables
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Troubadour
8/22/2013 2:30 AM
Lol, this is the funniest thread. I couldn't stop laughing
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Moon
8/23/2013 11:53 AM
ALTER TABLE dbo.Table1
ADD
Column1 int,
Column2 int
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
jdn
8/23/2013 12:57 PM
@Moon:

That's adding two columns, not altering them.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
clintonf
11/12/2013 6:48 AM
There is an extension to T-SQL which lets you do it. It's called "Nonsense SQL". It's available Nonsense SQL

Let's see how many do a google search for it.

Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
SQL Expert
12/18/2013 2:46 PM
Easy.

ALTER TABLE
table
ADD
col1 INT NULL
,col2 INT NULL;
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
jdn
12/18/2013 3:40 PM
@SQL Expert:

Yeah, we've already covered that. That's adding two columns, not altering two columns.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
SQL Expert
12/19/2013 8:30 AM
Just thought I'd add on to this five-year-old thread.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Wow
2/11/2014 4:06 PM
This comment thread is surreal. Either full of trolls or idiots.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
eug
2/20/2014 1:43 AM
in Oracle you can use ALTER TABLE x ALTER COLUMN.

In sql server you can use sys objects:

elect '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)
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
jdn
2/20/2014 9:06 AM
@eug:

Um, no.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Stunned
3/3/2014 9:12 AM
It boggles my mind how people can't digest a simple request.

I deal with this as a manager every day - you ask for X, but someone is hellbent on telling you Y.

The problem statement is this:

Can you do ALTER TABLE X ALTER COLUMN Y, Z, Q
without it being 3 separate operations.

The answer is no.

Only a maniac would go in and start hacking the sys tables.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Sunej
3/25/2014 3:20 PM
Very long rplies but answer is simple:

ALTER TABLE t
ADD c1 <datatype>
,cl <datatype>
,...
;

Why so arguing about simple question?
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
jdn
3/25/2014 4:17 PM
@Sunej:

Because people like you can't read the question?

You are ADDING multiple columns, not modifying existing ones.

Thanks for trying.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
pure_awesome
3/26/2014 10:02 AM
I wonder:

SELECT * DIFFERENCE ('Alter','Add') FROM THIS_POST
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
jdn
3/26/2014 1:50 PM
@pure_awesome:

LOL
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
DAbides
4/3/2014 2:10 PM
Hi mom!
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
jdn
4/3/2014 2:38 PM
@DAbides' Mom:

Why are you reading this blog? You should be able to find better use of your time.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
[OvO]wl
4/26/2014 5:52 PM
The syntax for this request in MySQL would be:

ALTER TABLE DELETEME
MODIFY VAT DECIMAL(3,2) NOT NULL,
MODIFY FINAL_PRICE DECIMAL(5,2) NOT NULL;
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
james
5/15/2014 9:48 AM
You guys are priceless!...:)

@Wow
this tread is full of them (both genius and idiots!)

jus' sayin...

james
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
SQL_Things
5/16/2014 6:21 AM
@james
You mean trolls, genius' and idiots!

then system_table_hacking maniacs

thank you guys!

SQL rocks!
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
moentjers
5/19/2014 5:16 AM
I stored this thread for whenever I need a happy moment in my life.

thank you all !
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
AJ
7/29/2014 2:05 AM
It's Just not possible to alter several columns in one statemet within T-sql. Switch to Oracle
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
SH
7/30/2014 3:54 AM
To all those who haven't read the original question try this :
USE MASTER
GO
ALTER DATABASE myproductiondatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE myproductiondatabase


Then go put your sql server back in the box and and go look for another non technical job :)
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Yong Mallare
8/31/2014 8:21 PM
Hahaha! Funny! Nice one jdn! This made my day, thanks guys!
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Ignatius
11/13/2014 10:50 PM
This might be the funniest thing I've read all year...
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Sachin
1/14/2015 3:05 PM
Indeed Funny.. I loved Tilt's comment :D
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Prajakta
2/18/2015 4:03 AM
Just came across this thread while searching and couldn't stop my laughing. Preserving this link to get relaxed during work pressure :D
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Carl
6/24/2015 3:53 PM
It's kind of sad that the chatter on this post has died down.

It was golden for a while when someone would take on the challenge (like the crackpots searching for a perpetual motion machine) to prove jdn's statement wrong.

too funny!

Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Maddy
8/19/2015 9:48 PM
1+1 = 3 :D
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Bob
8/26/2015 3:35 PM
This is easily the funniest SQL thread I've ever seen. It also answered my question. Thank you for this!

p.s.
Seems like a lot of developers out there fail at basic comprehension!

Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Vrush
9/10/2015 6:01 AM
Facing same issue. I was hopeful of getting an answer till the mid of this thread but then I forgot about it and started enjoying the funny trail. :P
And in the end realized the truth, there's nothing like altering multiple columns in single statement. Ahh SQL server!!
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
SybaseGuy
10/28/2015 2:40 AM
8 YEARS, WOW!!

The funniest SQL thread I have read!
No, it's not informative, save the first post.

After 8 years, the answer is STILL NO.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Anne
11/26/2015 6:47 AM
After 8 years nnooo!! i was looking for the answer to this. Surely someone needs to come up with how to do this (hopefully me), i have about 40 alter statements. Absolute nightmare
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
punya
2/12/2016 4:58 AM
ALTER TABLE organization_board_compensation
ALTER COLUMN committee_type TYPE int ,
ALTER COLUMN board_fee_category TYPE int ,
ALTER COLUMN board_fee_type TYPE int ,
ALTER COLUMN board_fee_unit TYPE int ,
ALTER COLUMN board_fee_role TYPE int ,
ALTER COLUMN board_fee_restriction TYPE int ,
ALTER COLUMN vesting_schedule TYPE int ;
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
jdan
2/12/2016 8:39 PM
@Punya:

That isn't valid T-SQL
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
sixpack
4/13/2016 8:48 AM
That thread is really, really, really funny !
Made my day. Thanks
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
Rob
1/9/2017 10:43 AM
Well, that was a fun 10 minutes. But I'm not sure whether or not you can modify (or add) multiple columns at the same time in a single alter table statement.

I'll check back later to see if there's an answer.
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
jdn
1/9/2017 12:36 PM
@Rob:

; )
Gravatar
# re: T-SQL: Can you alter multiple columns within a single alter table statement?
ihavenoideawhatimdoing
6/21/2017 5:21 PM
You won't find the answer with an Alter:

UPDATE sys.columns
SET name='cname', max_length = 9001, etc...
WHERE sys.columns.object_id =(SELECT o.object_id
FROM sys.objects o
JOIN sys.columns c on c.object_id = o.object_id
WHERE o.name IN ('table1','table2','table3')
AND c.name IN ('column1','column2','column3')

Post Comment

Title *
Name *
Email
Url
Comment *  
Please add 4 and 3 and type the answer here: