CODEWITHSUNDEEP
sqlsql-servercollation
The_Black_Smurf
The_Black_Smurf

Reputation: 5269

Refreshing SYS.columns after db collation change

Let's say your database collation is set to French_CI_AS and that you have an Employees table with a FirstName column collation set to database_default. If you run to following code to change the collation to Latin1_General_CI_AI, the DATABASEPROPERTYEX function will return Latin1_General_CI_AI while the sys.columns will still return French_CI_AS

ALTER DATABASE MyDB COLLATE Latin1_General_CI_AI;

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DatabaseCollation
--Return Latin1_General_CI_AI

SELECT collation_name AS FieldCollation
FROM sys.columns
WHERE OBJECT_ID IN(SELECT object_id FROM sys.objects WHERE type = 'U' AND NAME = 'Employees')
AND NAME = 'FirstName';
--return French_CI_AS

If you run the same code a few second later, the sys.columns will eventually be updated. However, is there a way to force an update of the sys.columns?

I already tried the sp_refreshsqlmodule and sp_refreshview StoredProc:

EXEC sp_refreshsqlmodule N'dbo.Employees'

and

EXEC sp_refreshview N'dbo.Employees'

but they both raise an error (tried with the sa account and without dbo):

Could not find object 'dbo.Employees' or you do not have permission.

Upvotes: 0

Views: 507

Answers (1)

SQLChao
SQLChao

Reputation: 7847

Altering the database collation doesn't change the existing columns. You need to ALTER TABLE

ALTER TABLE Employees
  ALTER COLUMN FirstName VARCHAR(20) COLLATE  Latin1_General_CI_AI

Upvotes: 2

Related Questions