Automatic cleanup of MSSQL Server Table collation Solution _mssql

In a team project, there is no agreement on collation, and programming in MS SQL Server encounters this problem:
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "sql_latin1_general_cp1_ci_as" in the equal to OP Eration.

Because of the coding problem, different encoded strings are not directly comparable, there are two solutions, 1 is specified in query with a collation comparison, the other is to modify the column collation type to avoid this error.

The second method, one column is very tired, write a script, in addition to being treated as constraints such as primary key, foreign key varchar, Char,nvarchar Can be uniformly modified into a collation ...

Full SQL code:

Copy Code code as follows:

DECLARE @CollationName varchar (500);
Set @CollationName = ' SQL_Latin1_General_CP1_CI_AS '
CREATE TABLE #tmp (sqlstr varchar (max));
INSERT INTO #tmp
Select ' ALTER TABLE [' + O.name + '] alter column [' + c.name + '] ' +
(Case c.system_type_id When 167 then ' varchar (' when 175 ' char (' else ' then ')
+ CONVERT (varchar,c.max_length) + ') collate ' + @CollationName
From Sys.columns C,
Sys.objects o
where c.object_id=o.object_id and o.type= ' U ' and c.system_type_id in (167,175,231) and collation_name<>@ CollationName
and C.name not in (
Select CC. column_name
From
Information_schema. Table_constraints PK,
Information_schema. Key_column_usage cc
where
Cc. table_name = PK. table_name
and CC. Constraint_name = pk. constraint_name)
while (exists (SELECT * from #tmp))
Begin
DECLARE @sqlStr varchar (max);
Select @sqlStr = (select top 1 sqlstr from #tmp);
EXEC (@sqlStr)
Delete from #tmp where sqlstr= @sqlStr
End
drop table #tmp;