How to alter Multiple columns in a table

Question

How to alter Multiple columns in a table

SreenivasaRao

SSC-Addicted

Points: 499
More actions
December 5, 2006 at 1:49 am

#115199

Hi
I know below query alter only one column
ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (200, 2)
then
How to alter multiple column in table at a time

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Only way to do that is to create a new table, copy the data over, drop the old table, rename the new. Then make sure that you replace the constraints and indexes.

Management studio/Enterprise manager can generate the code for you as it's how both do table modifications.

Otherwise it's multiple Alter Table Alter column statements.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

its not possible to use Alter Table statement for altering multiple columns.

"Keep Trying"

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

BTW, this doesn't work either :

CREATE TABLE #Demo

(

ID INT NOT NULL IDENTITY (1,1)

)

GO

ALTER TABLE #Demo

ALTER COLUMN ID INT NOT NULL IDENTITY(200,2)

GO

DROP TABLE #Demo

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

Good catch Remi - I'm glad that Celko didn't come across this one...he'd have launched into a tirade immediately....

**ASCII stupid question, get a stupid ANSI !!!**

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Let me find his phone number... I'll give him a ring .

Antares686 SSC Guru Points: 125444 More actions · Answer 6

Although the method used generally does work I have performed a script where a table was changed the way EM does it. After completion the table was gone. So make sure you have a backup beforehand. We restored ours and ran again without issue. The ALTER method even thou more time consuming has not done that to me before so weigh the difference and risks. Also a few large tables of mine have show to take longer with the EM method of new object, populate from original, drop original and rename new to original name.