alter multiple data type - 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

General SQL Server Forums

New to SQL Server Programming

alter multiple data type

Author

Topic

gongxia649
So Suave

344 Posts

Posted - 2006-04-20 : 16:18:32

how do you alter multiple data type in a table?

in oracle we have "modify"
is there something similar in sql server?

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-04-20 : 17:53:34

If you are talking about altering multiple columns at one time then you will have to recreate the table. To modify one at a time then use normal ALTER syntax:

Books online:

ALTER TABLE table
{ [ ALTER COLUMN column_name
{ new_data_type [ ( precision [ , scale ] ) ]
[ COLLATE < collation_name > ]
[ NULL | NOT NULL ]
| {ADD | DROP } ROWGUIDCOL }
]

Nathan Skerl

gongxia649
So Suave

344 Posts

Posted - 2006-04-21 : 08:39:21

im not looking for that.
i just wanna change the variable types

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-21 : 09:05:12

What do you means Variable Types??

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-21 : 09:43:49

so what exactly are you looking for if that's not it?

--------------------
keeping it simple...

gongxia649
So Suave

344 Posts

Posted - 2006-04-21 : 09:47:06

like:
alter table a
alter column column1 int
alter column column2 varchar (20)

Kristen
Test

22859 Posts

Posted - 2006-04-21 : 09:54:44

Easiest way, I reckon, if you are not familiar with the SQL Server syntax is to make the changes in Enterprise Manager using the GUI tools - right click the table and choose "Design"

Once you have made all your changes click the "Create change script" icon, cut & paste the script to Query Analyser [or whatever] and abandon the changes in Enterprise Manager.

IME that gives you the tightest script possible.

Kristen

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-04-21 : 12:56:30

quote:
alter table a
alter column column1 int
alter column column2 varchar (20)

In Oracle you would use Modify to do this. In SQL Server you use:

alter table a
alter column column1 int

alter table a
alter column colum2 varchar(20)

No shortcut that I know of. Also, Kristen's reckoning is right on. Make the changes in Enterprise Manager and use the generate SQL process to view the script necessary to propagate those changes. You will see that behind the scenes, to modify multiple columns at once, EM ~~creates~~ recreates the table:

BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_a
	(
	column1 int NULL,
	column2 varchar(20) NULL
	)  ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.a)
	 EXEC('INSERT INTO dbo.Tmp_a (column1, column2)
		SELECT CONVERT(int, column1), CONVERT(varchar(20), column2) FROM dbo.a (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.a
GO
EXECUTE sp_rename N'dbo.Tmp_a', N'a', 'OBJECT'
GO
COMMIT

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources