SQL Server Collation Setting

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%'

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.