Altering datatype for multiple columns

[CygNuS]

Ok, i'm using SQL Server 2005 and i'm trying to change the datatype of 4 columns in ARTIKELS.

This works for a single column

ALTER TABLE [dbo].[ARTIKELS]
ALTER COLUMN [OMSN] nvarchar(200)

But for all columns it doesn't:

ALTER TABLE [dbo].[ARTIKELS]
ALTER COLUMN [OMSN] nvarchar(200),
ALTER COLUMN [OMSF] nvarchar(200) NULL,
ALTER COLUMN [OMSE] nvarchar(200) NULL,
ALTER COLUMN [OMSD] nvarchar(200) NULL

This doesn't either:

ALTER TABLE [dbo].[ARTIKELS]
ALTER COLUMN [OMSN] nvarchar(200),
[OMSF] nvarchar(200) NULL,
[OMSE] nvarchar(200) NULL,
[OMSD] nvarchar(200) NULL

Writing 4 seperate alter statements is not that much of a problem, but it would really be simpler for later adjustments if i could pull it off in one statement.

Weird thing is: ALTER TABLE ARTIKELS ADD and DROP COLUMN works with multiple columns, so why doesn't ALTER COLUMN?

Am i asking the impossible? I couldn't find anything on the web offering a proper solution.

 

[cjard]

All my attempts to modify multiple columns have also failed. I would suggest that you modify them one at a time and seek to avoid making a habit of it

 

[CygNuS]

I will keep that in mind Thx for trying cjard, (i'll get back to implementing your sample "CygNuS"-project into my program now )

 

[cjard]

If it were Oracle, I would be able to tell you exactly how to write an SQL that writes sql..

Something like this:

SELECT 'ALTER TABLE ' || table_name || ' MODIFY COLUMN ' || column_name || ' nvarchar(200) NULL;'
FROM
user_tab_columns
WHERE
table_name LIKE 'APPS_PREFS';

would produce a result like:

ALTER TABLE APPS_PREFS MODIFY COLUMN PREF_APP nvarchar(200) NULL;
ALTER TABLE APPS_PREFS MODIFY COLUMN PREF_GROUP nvarchar(200) NULL;
ALTER TABLE APPS_PREFS MODIFY COLUMN PREF_NAME nvarchar(200) NULL;
ALTER TABLE APPS_PREFS MODIFY COLUMN PREF_VALUE nvarchar(200) NULL;
ALTER TABLE APPS_PREFS MODIFY COLUMN PREF_COMMENT nvarchar(200) NULL;

If you would like me to look into a corollarary for sql server, let me know.