December 01, 2020, 09:22:14 pm
News: If your question is answered please put [RESOLVED] in the subject of the initial message.
Pages: [1]   Go Down
Author Topic: SQL Server 2005 Alter Table Alter Column for multiple columns  (Read 14009 times)
Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« on: April 21, 2008, 10:21:54 am »

I need to alter the collation of a bunch of columns of different tables in a database (the developers declared the collation individually instead of doing it via the database default). I've got the basic syntax down and it works but the problem is that I don't know how or if it is possible to add multiple columns of a table to a single ALTER TABLE statement.

Right now I'm doing it:
Code: (sql)
ALTER TABLE dbo.table_name
     ALTER COLUMN column_name1 varchar(40) COLLATE ARABIC_CI_AS NOT NULL;
ALTER TABLE dbo.table_name
     ALTER COLUMN column_name2 varchar(30) COLLATE ARABIC_CI_AS NOT NULL;

Anyone know if it is possible to actually combine these two statements since they access the same table?
Logged

-- signature --

wrack
Master of TMEOotAP
*

Stars: +3/-0
Offline Offline

Posts: 11116


Don't look back. Look in the mirror to look back.


WWW
« Reply #1 on: April 22, 2008, 12:20:00 am »

Unfortunately no JR.

You have to stick with 1 statement at a time. In fact the way we do here is even a little more longer. We used to even do a further check to see if the type was different and collation was different before doing unnecessary changes.

Code: (sql)
IF EXISTS (SELECT c.Name FROM sysobjects o, syscolumns c WHERE o.id = c.id AND o.type = 'U' AND o.name = 'table_name' AND c.name LIKE 'column_name1')
BEGIN
ALTER TABLE dbo.table_name ALTER COLUMN column_name1 varchar(40) COLLATE ARABIC_CI_AS NOT NULL;
END;

Keep in mind that changing a collation will require to re-index the table.

Code: (sql)
USE database_name;
GO
ALTER INDEX ALL ON dbo.table_name REBUILD
GO
Logged

Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« Reply #2 on: April 22, 2008, 05:52:43 am »

Yeah I learned that the hard way and also undoing then redoing any constraints yuk However, it works now.

The problem was the company that produces our software swears up and down that it works fine if you use the SQL_Latin1_General_CP1_CI_AS collation for dealing with Arabic characters, and it doesn't and no matter how much I argue the fact they just can't seem to agree that it doesn't work like they think (I'm not even sure how the hell they are testing it).

So I created a conversion script to change the database default collation as well as the collation for all varchar and text data types yay.
Logged

-- signature --

wrack
Master of TMEOotAP
*

Stars: +3/-0
Offline Offline

Posts: 11116


Don't look back. Look in the mirror to look back.


WWW
« Reply #3 on: April 22, 2008, 11:17:33 pm »

Ahh good work JR  beerchug
Logged

Pages: [1]   Go Up
Print
 
Jump to: