is there a better way to change the collation 1

[eyhandle]

is there anyway to change the collation on all of the fields in a table at once. without alterting all of the field column names seperatly. is there a better way?? this is what i'm doing

go
ALTER TABLE table1 ALTER COLUMN column1
char(2) COLLATE SQL_Latin1_General_CP1255_CI_AS NULL
go
ALTER TABLE table1 ALTER COLUMN column2
char(15) COLLATE SQL_Latin1_General_CP1255_CI_AS NULL
...........................

 

[vadimg]

Nope, you have to change them one by one...

Here is the select that will at least get you an alter table script for all character fields in a chosen table.



select 'alter table '+o.name+' alter column '+c.name+' '+t.name+'('+cast(c.length as varchar(3))+') collate SQL_Latin1_General_CP1255_CI_AS ' + case c.isnullable when 0 then 'NOT NULL' else '' end+char(10)+'go'
from sysobjects o join syscolumns c
on o.id = c.id
join systypes t
on c.xtype = t.xtype
where t.name like '%char'
and o.id = object_id ('TABLENAME')

 

[SemperFiDownUnda]

most often you should change it at the server level.

 

[tlbroadbent]

SemperFiDownUnda,

I don't think many of us want to change the Server collation very often, if at all. Changing the server collation requires execution of the Rebuild Master utility. That's a pretty drastic step. In SQL 2000, it is much easier to ALTER the collation of a database or individual columns in table. Most of the time, I believe the collation changes will be made at the column level. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm