how to change the collate to all the columns of the database?

Question

I would like to change the collation of all the columns of all the tables of my database. In stack overflow, I have found this script: (post)

SELECT 'ALTER TABLE [' + SYSOBJECTS.Name + '] ALTER COLUMN [' + SYSCOLUMNS.Name + '] ' + SYSTYPES.name +      CASE systypes.NAME     WHEN 'text' THEN ' '     ELSE     '(' + RTRIM(CASE SYSCOLUMNS.length     WHEN -1 THEN 'MAX'     ELSE CONVERT(CHAR,SYSCOLUMNS.length)     END) + ') '      END      + ' ' + ' COLLATE Latin1_General__CI_AI ' + CASE ISNULLABLE WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END     FROM SYSCOLUMNS , SYSOBJECTS , SYSTYPES     WHERE SYSCOLUMNS.ID = SYSOBJECTS.ID     AND SYSOBJECTS.TYPE = 'U'     AND SYSTYPES.Xtype = SYSCOLUMNS.xtype     AND SYSCOLUMNS.COLLATION IS NOT NULL     AND NOT ( sysobjects.NAME LIKE 'sys%' )     AND NOT ( SYSTYPES.name LIKE 'sys%' )     GO 

However, when I see the collation of the columns I see that the collation is the old collation.

The actual collation is AS, so I can have "ANIMAL" and "ÁNIMAL". When I execute the script, I don't get any error. I think that I would get an error because the new collation is AI. So this makes me think that the script does nothing.

How can I change the collation of all the columns of all my tables in the database?

Thanks.

Answer 1

Try this one -

Query:

DECLARE @collate SYSNAME SELECT @collate = 'Cyrillic_General_CS_AS'  SELECT        '[' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] -> ' + c.name     , 'ALTER TABLE [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + ']         ALTER COLUMN [' + c.name + '] ' +         UPPER(t.name) +          CASE WHEN t.name NOT IN ('ntext', 'text')              THEN '(' +                  CASE                      WHEN t.name IN ('nchar', 'nvarchar') AND c.max_length != -1                          THEN CAST(c.max_length / 2 AS VARCHAR(10))                     WHEN t.name IN ('char', 'varchar') AND c.max_length != -1                          THEN CAST(c.max_length AS VARCHAR(10))                     WHEN t.name IN ('nchar', 'nvarchar', 'char', 'varchar') AND c.max_length = -1                          THEN 'MAX'                     ELSE CAST(c.max_length AS VARCHAR(10))                  END + ')'              ELSE ''          END + ' COLLATE ' + @collate +          CASE WHEN c.is_nullable = 1              THEN ' NULL'             ELSE ' NOT NULL'         END FROM sys.columns c JOIN sys.objects o ON c.[object_id] = o.[object_id] JOIN sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id WHERE t.name IN ('char', 'varchar', 'text', 'nvarchar', 'ntext', 'nchar')     AND c.collation_name != @collate     AND o.[type] = 'U' 

Output:

-------------------------------------------------- ------------------------------------------------------------------------------------------------------------------ [dbo].[CategoryType] -> CategoryTypeCD          ALTER TABLE [dbo].[CategoryType] ALTER COLUMN [CategoryTypeCD] VARCHAR(20) COLLATE Cyrillic_General_CI_AS NOT NULL [dbo].[Employee] -> TabelNumber                 ALTER TABLE [dbo].[Employee] ALTER COLUMN [TabelNumber] VARCHAR(12) COLLATE Cyrillic_General_CI_AS NULL [dbo].[Supplement] -> WorkFactorCD              ALTER TABLE [dbo].[Supplement] ALTER COLUMN [WorkFactorCD] VARCHAR(50) COLLATE Cyrillic_General_CI_AS NOT NULL [dbo].[Surcharge] -> WorkFactorCD               ALTER TABLE [dbo].[Surcharge] ALTER COLUMN [WorkFactorCD] VARCHAR(50) COLLATE Cyrillic_General_CI_AS NOT NULL [dbo].[Surcharge] -> Condition                  ALTER TABLE [dbo].[Surcharge] ALTER COLUMN [Condition] NVARCHAR(MAX) COLLATE Cyrillic_General_CI_AS NULL [dbo].[WorkFactor] -> WorkFactorCD              ALTER TABLE [dbo].[WorkFactor] ALTER COLUMN [WorkFactorCD] VARCHAR(50) COLLATE Cyrillic_General_CI_AS NOT NULL [dbo].[WorkFactor] -> Name                      ALTER TABLE [dbo].[WorkFactor] ALTER COLUMN [Name] NVARCHAR(200) COLLATE Cyrillic_General_CI_AS NOT NULL [dbo].[WorkOut] -> WorkShiftCD                  ALTER TABLE [dbo].[WorkOut] ALTER COLUMN [WorkShiftCD] NVARCHAR(40) COLLATE Cyrillic_General_CI_AS NULL [dbo].[WorkOut] -> AbsenceCode                  ALTER TABLE [dbo].[WorkOut] ALTER COLUMN [AbsenceCode] VARCHAR(50) COLLATE Cyrillic_General_CI_AS NULL [dbo].[WorkOut] -> PaymentType                  ALTER TABLE [dbo].[WorkOut] ALTER COLUMN [PaymentType] CHAR(4) COLLATE Cyrillic_General_CI_AS NULL