1

In the SQL server, the default collation is SQL_Latin1_General_CP1_CI_AS, previously, I used to specify the collation explicitly at the end of each query to SQL_Latin1_General_CP1_CI_AI to ignore the accents when comparing strings.

Now, I wanted to implement a generic solution, so that, I changed the database collation to SQL_Latin1_General_CP1_CI_AI but the queries are still not working unless I specify the collation explicitly! I searched a bit for this issue and I knew that the columns that were created previously using the old collation, have to be updated as well to the new collation or have to be dropped and recreated again!

Is there any idea to handle this issue without updating all columns one by one?

Here is a sample of the queries that I have With specifying the collation:

select * from Members where FirstName like '%cafe%' collate SQL_Latin1_General_CP1_CI_AI

Without specifying the collation:

select * from Members where FirstName like '%cafe%'
Ⲁⲅⲅⲉⲗⲟⲥ
  • 608
  • 1
  • 8
  • 31
  • If your column is in a different collation then when the comparison occurs then that column's collation will be used. If your columns are in the wrong collation then changing their collation is the correct solution, however, that isn't as simple task as just changing them, as some dependant objects will need to be `DROP`ed and re`CREATE`d. – Thom A Aug 14 '23 at 09:19
  • I just want the simplest way to ignore the accents when comparing the strings, that's it! If I couldn't find any simple way, I'll have to update all columns' collations – Ⲁⲅⲅⲉⲗⲟⲥ Aug 14 '23 at 09:23
  • Changing the column's collation is what you want to do, as syntax using `COLLATE` isn't SARGable. – Thom A Aug 14 '23 at 09:26

1 Answers1

0

You can generate a script to change the collation of all columns in all tables using the following code:

DECLARE @fromCollation sysname = 'collation_name_here', @toCollation sysname = 'collation_name_here';

DECLARE @sql nvarchar(max);

SELECT
  CONCAT(
    'ALTER TABLE ',
    QUOTENAME(s.name),
    '.',
    QUOTENAME(t.name),
    ' ALTER COLUMN ',
    QUOTENAME(c.name),
    ' ',
    typ.name,
    '(',
    c.max_length,
    ') COLLATE ',
    @toCollation,
    IIF(c.is_nullable = 1, ' NULL', ' NOT NULL')
  )
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
JOIN sys.columns c ON c.object_id = t.object_id
JOIN sys.types typ ON typ.user_type_id = c.user_type_id
WHERE c.collation_name = @fromCollation
  AND typ.name IN ('varchar', 'nvarchar', 'char', 'nchar');

If you have any non-clustered indexes, or any schema-bound functions or views, you would need to drop and re-create them.

Charlieface
  • 52,284
  • 6
  • 19
  • 43