changing collation for a database

Question

changing collation for a database

bala2

SSCertifiable

Points: 5547
More actions
November 14, 2013 at 5:16 am

#300259

Hi,
On our Production server for 1 database, Client want to change the collation setting to French language as the application server is running with French language.
I think we can change the database collation by using below command.
ALTER DATABASE Admin
COLLATE NewCollationname ;
GO
But my doubt is it enough to change only database collation or do we need to change collation for objects also…..
Would the application support such a configuration (SQL installation, system tables etc with English settings, database with French settings
Please help me to find the right steps to fix the issue.
Advance thanks...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

All the alter database does is change the default collation that new objects will use. If you want to change existing objects, you need to run an ALTER TABLE ALTER COLUMN on every single string column in that database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bala2 SSCertifiable Points: 5547 More actions · Answer 2

We have more than 50 tables on that database.

Is there any other way to do that or

TO change the collation all at a time.

THanks.

Lowell SSC Guru Points: 323506 More actions · Answer 3

no easy way.

you can use the sys.columns view to generate the suite of commands, and the commands might fail if there are any check constraints, default constraints , foreign keys or calculated fields which also feature the columns you need to change the collation of.

something like this can help get you started, but i can turn into a big job; i wrote a test script once that was like 1000 lines of code, just trying to account for all possible variables like the constraints i mentioned:

DECLARE @NewCollation VARCHAR(50)

SET @NewCollation = 'Latin1_General_CI_AS' -- change this to the collation that you need

--WAS 'SQL_Latin1_General_CP1_CI_AS'

--toggling 'Latin1_General_CI_AS'

SELECT

objz.object_id,

SCHEMA_NAME(objz.schema_id) AS SchemaName,

objz.name AS TableName,

colz.name AS ColumnName,

colz.collation_name,

colz.column_id,

'ALTER TABLE '

+ QUOTENAME(SCHEMA_NAME(objz.schema_id))

+ '.'

+ QUOTENAME(objz.name)

+ ' ALTER COLUMN '

+ CASE

WHEN colz.[is_computed] = 0

THEN QUOTENAME(colz.[name]) + ' ' + ( TYPE_NAME(colz.[user_type_id]) )

+ CASE

WHEN TYPE_NAME(colz.[user_type_id]) IN ( 'char', 'varchar' )

THEN

CASE

WHEN colz.[max_length] = -1

THEN '(max)' + SPACE(6 - LEN(CONVERT(VARCHAR, colz.[max_length]))) + SPACE(7) + SPACE(16 - LEN(TYPE_NAME(colz.[user_type_id])))

----collate to comment out when not desired

+ CASE

WHEN colz.collation_name IS NULL

THEN ''

ELSE ' COLLATE ' + @NewCollation -- this was the old collation: colz.collation_name

END + CASE

WHEN colz.[is_nullable] = 0

THEN ' NOT NULL'

ELSE ' NULL'

END

ELSE '(' + CONVERT(VARCHAR, colz.[max_length]) + ') ' + SPACE(6 - LEN(CONVERT(VARCHAR, colz.[max_length]))) + SPACE(7) + SPACE(16 - LEN(TYPE_NAME(colz.[user_type_id])))

----collate to comment out when not desired

+ CASE

WHEN colz.collation_name IS NULL

THEN ''

ELSE ' COLLATE ' + @NewCollation

END

+ CASE

WHEN colz.[is_nullable] = 0

THEN ' NOT NULL'

ELSE ' NULL'

END

WHEN TYPE_NAME(colz.[user_type_id]) IN ( 'nchar', 'nvarchar' )

THEN

CASE

WHEN colz.[max_length] = -1

THEN '(max)' + SPACE(6 - LEN(CONVERT(VARCHAR, (colz.[max_length] / 2)))) + SPACE(7) + SPACE(16 - LEN(TYPE_NAME(colz.[user_type_id])))

----collate to comment out when not desired

--+ CASE

-- WHEN colz.collation_name IS NULL

-- THEN ''

-- ELSE ' COLLATE ' + colz.collation_name

-- END

+ CASE

WHEN colz.[is_nullable] = 0

THEN ' NOT NULL'

ELSE ' NULL'

END

ELSE '(' + CONVERT(VARCHAR, (colz.[max_length] / 2)) + ') ' + SPACE(6 - LEN(CONVERT(VARCHAR, (colz.[max_length] / 2)))) + SPACE(7) + SPACE(16 - LEN(TYPE_NAME(colz.[user_type_id])))

----collate to comment out when not desired

--+ CASE

-- WHEN colz.collation_name IS NULL

-- THEN ''

-- ELSE ' COLLATE ' + colz.collation_name

-- END

+ CASE

WHEN colz.[is_nullable] = 0

THEN ' NOT NULL'

ELSE ' NULL'

END

END --iscomputed = 0

+ ';' AS Command

FROM sys.columns colz

INNER JOIN sys.tables objz

ON colz.object_id = objz.object_id

WHERE colz.collation_name IS NOT NULL --

AND objz.is_ms_shipped = 0

AND colz.is_computed = 0

AND colz.collation_name <> @NewCollation

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

bala2 (11/14/2013)
We have more than 50 tables on that database.
Is there any other way to do that or
TO change the collation all at a time.

If you want to change the collation on existing objects you need to run an ALTER TABLE ALTER COLUMN on every single column of char/varchar data type.

Don't forget you'll need to drop indexes and possibly constraints before and re-create them afterwards

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass