Just this week I had opportunity to change collation of all objects using it in a database without dropping it. I like to computers to do what computers should do - work that is :). So I created a query that gave me script changing collation of all columns using collation - varchar, char and so on.
I have got 3900 lines altering tables and columns. Nice, but not so easy, this script was not working at first F5 - you can't change collation of a column that is a part of constraint or index - you have do drop them. So I had to wade through the script and add statements dropping constraints and indexes and recreating them after modifications had been done. To find tables that were causing problems I simply executed script - execution was stopped on column whose collation could not be changed and I had exact information what should be done.
This is the query that generates alter table .. alter column statements:
select 'alter table [' + s.name + '].[' + t.name + '] alter column [' + c.name + '] ' +
ty.name + case when ty.name not in ('text', 'sysname') then '(' + case when c.max_length > 0 then
case when ty.name not in ('nchar', 'nvarchar') then convert(varchar, c.max_length) else convert(varchar, c.max_length/2) end else 'max' end +
')' else '' end + ' collate SQL_Latin1_General_CP1_CI_AS ' +
case when c.is_nullable = 0 then 'NOT ' else '' end + 'NULL'
from (sys.columns c inner join sys.types ty on c.system_type_id = ty.system_type_id) inner join
(sys.objects t inner join sys.schemas s on t.schema_id = s.schema_id) on c.object_id = t.object_id
where t.type='U' and c.collation_name is not null and ty.name <> 'sysname'
order by s.name, t.name, c.column_id
I saved loads of time thanks to this script.