The main problem you will have is when you use Tempdb which will have the server deafault collation.
If you are going to convert your database you will have problems converting columns that are used in indexes, foreign keys, and computed columns. You could drop all of these objects change collation to server default then rebuild them. You should test this
first of course. I have had to do this before and it went through ok. The script below if run in the context of your database will create an alter script for every column which needs to be changed -when you reun the results they will alter the tabls and change
collation per column. You will hit problems on indexes etc as I mentioned earlier. If you don't have indexes on these columns you may be ok.
Remember please do this in test and make sure you rapplication is happy before applying to live.
select'alter table [' + s.name + '].[' + t.name + '] alter column [' + c.name + '] ' +
ty.name + casewhen ty.name notin ('text', 'sysname') then'(' + casewhen c.max_length > 0 thencasewhen ty.name notin ('nchar', 'nvarchar') thenconvert(varchar, c.max_length) elseconvert(varchar, c.max_length/2) endelse'max'end +
casewhen c.is_nullable = 0 then' NOT 'else''end + ' NULL'from (sys.columns c innerjoin sys.types ty on c.system_type_id = ty.system_type_id) innerjoin
(sys.objects t innerjoin 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 isnotnulland ty.name <> 'sysname'AND c.collation_name <> 'SQL_Latin1_General_CP1_CI_AS'orderby s.name, t.name, c.column_id;
(SQL_Latin1_General_CP1_CI_AS was the default collation for SQL 2000)