change collation on all varchar fields in database

You might find some scripts that try to do this, but there are TOO many things that won't work in a automated way.
The preferred method is to create a new database with all the new collations in place, and copy over the database data.
This is a job you won't run many times on a database...
see here:
http://support.microsoft.com/?kbid=325335#7

CHeers

the following SQL will generate ALTER statements for you, which will work in the simplest of cases.
However, as angelIII says, a lot can go wrong - particularly, you cannot alter collation on an indexed column, or a column that has a FK relationship. If you have 1 or 2 indexes, try dropping them and recrating them afterwards.  If you have a lot of these, you're probably best recreating the whole database, as stated.

select 'alter table [' + table_schema + '].[' + table_name + '] alter column [' +
column_name + '] ' + data_type + '(' + convert(varchar(4),character_maximum_length) + ')' +
'collate DANISH_NORWEGIAN_CI_AS' +
case when is_nullable = 'YES' then ' NULL' else ' NOT NULL' end
from information_schema.columns
where data_type in ('varchar','char', 'nvarchar', 'nchar')

This should work (please excuse my shoddy sql ;)



declare @tabname sysname, @colname sysname, @collen int, @colnull int
declare @sqlstr nvarchar(1000)
declare mycur cursor for
 select so.name,sc.name, sc.length, sc.isnullable from sysobjects so (nolock), syscolumns sc (nolock),systypes st (nolock)
 where so.type='U' and sc.id=so.id and st.xtype=sc.xtype and st.name='varchar'
open mycur
fetch next from mycur into @tabname, @colname, @collen, @colnull
while @@fetch_status=0
begin
 set @sqlstr='ALTER TABLE '+rtrim(@tabname)+' ALTER COLUMN '+rtrim(@colname)+
            ' varchar('+rtrim(@collen)+')COLLATE DANISH_NORWEGIAN '
 if @colnull=1
      set @sqlstr=@sqlstr+' NULL'
 else
   set @sqlstr=@sqlstr+' NOT NULL'
print @sqlstr
 exec sp_executesql @sqlstr
 fetch next from mycur into @tabname, @colname, @collen, @colnull
end
close mycur
deallocate mycur

ok, I tried creating a new database, and then copy the data from the old to the new one, but it failed. Both the choices copy tables and views and copy objects and data, failed.

ok, I will try that thanks

>>Both the choices copy tables and views and copy objects and data, failed.
this is obvious because the collections between the databases differ.
As BillAn1 indicates, first create the objects with the new collation, and then copy the data
CHeers