4

I have a SQL Server database where the collation is set on it. However I am running into problems as the collation on some columns in the database varies from the database collation.

I would like to remove collation from the columns and use the collation set on the database.

I am working with SQL Server 2012.

How can I do this? Can a script do this?

2 Answers 2

14

With this script here, you can find all columns that don't match the database default:

DECLARE @DatabaseCollation VARCHAR(100)

SELECT 
    @DatabaseCollation = collation_name 
FROM 
    sys.databases
WHERE 
    database_id = DB_ID()

SELECT 
    @DatabaseCollation 'Default database collation'

SELECT 
    t.Name 'Table Name',
    c.name 'Col Name',
    ty.name 'Type Name',
    c.max_length,
    c.collation_name,
    c.is_nullable
FROM 
    sys.columns c 
INNER JOIN 
    sys.tables t ON c.object_id = t.object_id
INNER JOIN 
    sys.types ty ON c.system_type_id = ty.system_type_id    
WHERE 
    t.is_ms_shipped = 0
    AND 
    c.collation_name <> @DatabaseCollation

For each of those columns, you can then execute this simple command to change their collation to be the database default:

ALTER TABLE dbo.YourTable
ALTER COLUMN YourColumn VARCHAR(50) COLLATE DATABASE_DEFAULT

If you really really want, you could probably also combine the two - use a cursor on the SELECT statement from the first script and then iterate over the results and execute the second statement for each column found - I typically prefer to do this manually, to be more aware of what I'm doing (and how much I'm changing):

1
  • 3
    One thing to note: you cannot change the collation of a column that is part of an index or constraint. You will need to drop the index/constraint before being able to change the collation - which requires a bit more scripting.
    – user1822
    May 13, 2013 at 7:24
4

Here is the code for changing the database collation, it will change the collation of whole database but not of single object.

SELECT 'ALTER TABLE ' + SYSOBJECTS.Name + ' ALTER COLUMN ' + SYSCOLUMNS.Name + ' ' + 
SYSTYPES.name + '(' + RTRIM(CONVERT(CHAR,SYSCOLUMNS.length)) + ') ' + ' COLLATE [Replace Actual Collation]' + CASE ISNULLABLE WHEN 0 THEN 'NOT NULL' ELSE 'NULL' End +
CHAR(13) + ' GO'
FROM SYSCOLUMNS , SYSOBJECTS , SYSTYPES
WHERE SYSCOLUMNS.ID = SYSOBJECTS.ID
AND SYSOBJECTS.TYPE = 'U'
AND SYSTYPES.Xtype = SYSCOLUMNS.xtype
AND SYSCOLUMNS.COLLATION IS NOT NULL
GO

I've taken the code from the following link.

http://samsudeenb.blogspot.com/2007/01/changing-database-collation.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.