To give you just an example, we can start finding out what character columns are using different collations from SQL_Latin1_General_CP1_CI_AS that we need to change in order to make everything uniform. I am going to display a useful script to do it. In this example I am assuming that we want to use SQL_Latin1_General_CP1_CI_AS for all objects in the database server.
EXEC sp_MSforeachdb ' USE [?] select db_name(),c.name from sys.columns c inner join sys.types t on t.user_type_id= c.user_type_id inner join sys.tables tb on c.object_id=tb.object_id where c.collation_name is not null and t.is_user_defined=0 and tb.is_ms_shipped=0 and tb.name<>''sysdiagrams'' and c.collation_name<>''SQL_Latin1_General_CP1_CI_AS'' order by tb.name, c.column_id'
EXEC sp_MSforeachdb ' USE [?] if db_name() not in (''master'',''tempdb'',''msdb'',''model'') begin select replace( REPLACE( ''ALTER TABLE '' + QUOTENAME(SCHEMA_NAME(tb.schema_id)) + ''.'' + QUOTENAME(tb.name) + '' ALTER COLUMN '' + QUOTENAME(c.name) + '' '' + QUOTENAME(t.name) + ''('' + CAST( case when T.NAME=''NVARCHAR'' THEN c.max_length/2 WHEN T.NAME=''NCHAR'' THEN c.max_length/2 ELSE c.max_length END AS VARCHAR(10)) +'')'' + '' COLLATE SQL_Latin1_General_CP1_CI_AS'' + CASE WHEN c.is_nullable =1 THEN '' NULL '' else '' NOT NULL ;'' END, ''-1'', ''MAX'' ), ''[text](16)'', ''[varchar](max)'') as cmd INTO #TblTMP from sys.columns c inner join sys.types t on t.user_type_id= c.user_type_id inner join sys.tables tb on c.object_id=tb.object_id where c.collation_name is not null and t.is_user_defined=0 and tb.is_ms_shipped=0 and tb.name<>''sysdiagrams'' and c.collation_name<>''SQL_Latin1_General_CP1_CI_AS'' order by tb.name, c.column_id declare @cmd varchar(max) declare c_cmd cursor for select cmd from #TblTMP open c_cmd fetch next from c_cmd into @cmd while (@@fetch_status=0) begin exec( @cmd) fetch next from c_cmd into @cmd end close c_cmd deallocate c_cmd drop table #TblTMP end'
