Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

migrated from stackoverflow.com May 13 '13 at 7:08

This question came from our site for professional and enthusiast programmers.

2 Answers

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):

share|improve this answer
2  
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. –  a_horse_with_no_name May 13 '13 at 7:24
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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