CODEWITHSUNDEEP
sqlsql-servercollation
Yalin
Yalin

Reputation: 345

Collate column to SQL default collation

I'm trying to collate a column to the SQL default collation, but can't make it work.

All answers I see are related to database default and not the SQL default

I tried this Alter table x alter column name nvarchar(max) COLLATE DATABASE_DEFAULT but it will give me the database default which is different from the SQL default. (Hebrew vs SQL_LATIN)

I'm also trying to do it with select, but it seems impossible.

This is what I have tried : Alter table x alter column name nvarchar(max) COLLATE (Select SERVERPROPERTY('collation'))

Upvotes: 0

Views: 2608

Answers (2)

yoma
yoma

Reputation: 369

As @Serg said you can use dynamic sql. Try the following code:

SELECT  [name], [collation_name] 
FROM    sys.columns 
WHERE   [object_id] = OBJECT_ID( N'x.dbo.x', N'U') /*table name*/
    AND [name] = 'name'                            /*column name*/

DECLARE @sql nvarchar(max)
,       @ServerCollation nvarchar(100)

SELECT @ServerCollation = CAST( SERVERPROPERTY( N'Collation') AS nvarchar(max))
SELECT @sql = 'ALTER TABLE dbo.x ALTER COLUMN [name] nvarchar(max) COLLATE ' + @ServerCollation
EXECUTE sp_executesql @sql

SELECT  [name], [collation_name] 
FROM    sys.columns 
WHERE   [object_id] = OBJECT_ID( N'x.dbo.x', N'U') 
    AND [name] = 'name'

Upvotes: 4

navylover
navylover

Reputation: 13539

To check server level collation, use:

 SELECT CONVERT (varchar(256), SERVERPROPERTY('collation'));

To alter column lever collations, use:

ALTER TABLE myTable ALTER COLUMN mycol NVARCHAR(10) COLLATE Greek_CS_AI;  //Replace Greek_CS_AI with the actual value.

Upvotes: -1

Related Questions