Unable to update sys.columns - any other approach?

Question

Hello!

I just found out, that since I created my DB on a different server that had a different collation, then immigrated to a new server with new collation, now I keep on having trouble, and I decided to change all the values of the old collation.

So I tried to execute this query:

sp_configure "Allow Updates", 1
GO
RECONFIGURE WITH OVERRIDE
GO

UPDATE sys.columns SET collation_name = 
    'SQL_Latin1_General_CP1_CI_AS' WHERE collation_name = 'Hebrew_CI_AS'
GO

sp_configure "Allow Updates", 0
GO
RECONFIGURE
GO

But here is the output of the query:

Configuration option 'allow updates' changed from 0 to 1. 
    Run the RECONFIGURE statement to install.

Msg 259, Level 16, State 1, Line 2
Ad hoc updates to system catalogs are not allowed.

Configuration option 'allow updates' changed from 1 to 0. 
    Run the RECONFIGURE statement to install.

UPDATE
According to the answers bellow, I am just looking for an automated way to perform the action.

Answer 1

You can use alter table to change the collation for each column.

The only way to change a database collation is to drop and recreate the database.

Answer 2

You need to issue ALTER TABLE commands in order to change the collations of the specific columns.

e.g.

ALTER TABLE YourTable 
ALTER COLUMN ColA VARCHAR(10) COLLATE Latin1_General_CI_AS NOT NULL

Check out this MSDN reference

Answer 3

This query is generates a script that will swap all Hebrew_CI_AS collated columns to Latin1_General_CI_AS collation:

DECLARE @Script varchar(MAX)
SET @Script = CAST((
SELECT 'ALTER TABLE [' + Tables.Name + '] ALTER COLUMN [' + 
  [Columns].Name + '] ' + Types.Name + '(' + 
  CAST([Columns].max_length AS varchar) + ') COLLATE Latin1_General_CI_AS ' + 
  CASE WHEN [Columns].is_nullable = 0 THEN 'NOT ' ELSE '' END + 'NULL '
FROM sys.tables Tables INNER JOIN sys.all_columns [Columns]
  ON [Tables].[object_id] = [Columns].[object_id]
  INNER JOIN sys.types Types ON [Columns].system_type_id = Types.system_type_id
WHERE [Columns].collation_name = 'Hebrew_CI_AS'
FOR XML PATH('')
) AS varchar(MAX))

PRINT @Script
EXEC(@Script)

NOTE: For columns that are indexed/constrained you'll need to edit manually, but that's also something since when executing the above query the error-result (for a constraint etc.) contains the table & column name, you must admit it's still better than do everything manually.