How to automatically clear the MSSQL Server Table Collation Problem

In a team project, there is no agreement on Collation, programming in the ms SQL Server will encounter such a problem:
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL _Latin1_General_CP1_CI_AS" in the equal to operation.

Because of the encoding problem, strings of different encodings cannot be directly compared. There are two solutions to this problem. 1 is to specify a collation for comparison in the query, the other is to modify the collation type of the column to avoid this error.

In the second method, one column is very tired, and a script is written. In addition to the varchar, char, and nvarchar used as constraints such as primary key and foreign key, you can change them to a collation...

Complete SQL code:

Copy codeThe Code is 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 then' char ('else' nvarchar ('end)
+ 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;