ALTER COLUMN statement - 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)

ALTER COLUMN statement

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-11-14 : 10:31:12

Hey All,

I'm currently alter columns on a table, and wondered if there is a way to alter multiple columns using one Alter table statement?
i.e:

ALTER TABLE Tbl ALTER COLUMN Field1 Datetime, Field2 decimal(18,2) etc..

Rather than

ALTER TABLE Tbl ALTER COLUMN Field1 Datetime
ALTER TABLE Tbl ALTER COLUMN Field2 decimal(18,2)

I know it's lazy on my part, just wanted to know if it can be done.

Thanks!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-14 : 11:40:06

No, it is not possible.

No, you're never too old to Yak'n'Roll if you're too young to die.

prakum
Starting Member

16 Posts

Posted - 2009-11-16 : 05:14:59

hey dnf999
its possible....

To modify multiple columns in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
MODIFY (column_1 column_type,
column_2 column_type,
...
column_n column_type );

For example:

ALTER TABLE supplier
MODIFY (supplier_name varchar2(100) not null,
city varchar2(75) );

Praveen Kumar

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-16 : 05:19:14

quote:
Originally posted by prakum

hey dnf999
its possible....

To modify multiple columns in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
MODIFY (column_1 column_type,
column_2 column_type,
...
column_n column_type );

For example:

ALTER TABLE supplier
MODIFY (supplier_name varchar2(100) not null,
city varchar2(75) );

Praveen Kumar

Did you know this is for MS SQL Server?

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-16 : 05:29:52

quote:
Originally posted by prakum

hey dnf999
its possible....

To modify multiple columns in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
MODIFY (column_1 column_type,
column_2 column_type,
...
column_n column_type );

For example:

ALTER TABLE supplier
MODIFY (supplier_name varchar2(100) not null,
city varchar2(75) );

Praveen Kumar

MODIFY is ORACLE...

No, you're never too old to Yak'n'Roll if you're too young to die.

prakum
Starting Member

16 Posts

Posted - 2009-11-16 : 06:13:40

Sorry guys my mistake...
MODIFY doesnot work in MS SQL.It works only in ORACLE....There is no method to perform multiple column alter in MS SQL......

Praveen Kumar

Pradip
Starting Member

32 Posts

Posted - 2009-11-16 : 07:48:02

The only way to alter each column alter it separately. like
ALTER TABLE dbo.Employee
ALTER COLUMN FirstName VARCHAR(100) NOT NULL

ALTER TABLE dbo.Employee
ALTER COLUMN DateHired SMALLDATETIME NULL

ALTER TABLE dbo.Employee
ALTER COLUMN MiddleName VARCHAR(100) etc..

pradipjain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources