Need help on Alter Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help on Alter Column

Author

Topic

wkm1925
Posting Yak Master

207 Posts

Posted - 2010-01-25 : 01:23:30

Alter Table BusType Alter Column Col1 tinyint not null -- this statement is accurate

I want to alter column more than 1 column
Alter Table BusType
Alter Column (Col1 tinyint not null,Col2 tinyint not null) -- this statement got incorrect syntax

need help

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-25 : 01:43:46

An Alter Table ... Alter column can only alter 1 column per statement. So to alter 2 columns, you need two alter table statements

--
Gail Shaw
SQL Server MVP

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-25 : 01:44:18

Alter Multiple column in a single alter command is not possible.

You need to write twice.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

wkm1925
Posting Yak Master

207 Posts

Posted - 2010-01-25 : 01:47:29

ok. tq very much

Kristen
Test

22859 Posts

Posted - 2010-01-25 : 02:32:16

You can:

Create a new, temporary, table with correct structure
Copy data from Old to New table
Drop old table
Rename New table to original table name

If you use SSMS Table Design Tools you can generate a script (rather than just pressing "Save" for SSMS to make the changes for you) which will have all the correct SQL DDL statements to make the change - including drop/recreating FKs, Indexes, etc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources