Peter Larsson Blog

Patron Saint of Lost Yaks

Change collation

by Peter Larsson in  , ,

Yesterday I hade the unfortenate task to change the database collation for a particular database.
Not only the default database collation should be changed, but also all columns!

After some investigating about how to do this, I noticed that check constraints and foreign key constraints must be removed before changing a collation. Also indexes needed to be removed for this operation to succeed.

I did it manually since it was my first time, but I wrote down the steps and today I gathered the notes into a beta-script, just in case I need to do this again in the future

Why a script? Because most common given answer to this task is to create a new database with the correct collation, bcp out all data and import all data in the new database. Then there is the hassle involved to drop the old database, change name for the new database, change logical and eventually the physical file names. A lot of work even with this approach.

So, here is the script http://developerworkshop.net/Scripts/Collation.sql and please post back or email me any comment you have about this script.

Have fun!

//Peso

PS. You use the script on your own risk and I make no guarantee that it will work 100%. It is still in beta.

Legacy Comments
Charlie
2009-08-20		 re: Change collation
Hi Peso,

I may need to do exactly this in the future so this looks like something I'd be interested in.

I've taken your beta and ran a few test cases. I'm running into problems dropping (and probably recreating FK where there are multiple columns referenced)

For example this Key breaks your script as it tries to drop it twice.

USE [CollationChange_XXX]
GO
ALTER TABLE [dbo].[TabGroupItems] WITH CHECK ADD CONSTRAINT [FK_TabGroupItems_TabGroup] FOREIGN KEY([companyId], [tabGroupName])
REFERENCES [dbo].[TabGroup] ([companyId], [name])
GO
ALTER TABLE [dbo].[TabGroupItems] CHECK CONSTRAINT [FK_TabGroupItems_TabGroup]

This is turned into two DROP lines by this select statement which is lifted from your CTE:


SELECT
o.[schema_Id] AS [schema_Id]
, QUOTENAME(o.[name]) AS [CONSTRAINT_NAME]
, QUOTENAME(SCHEMA_NAME(po.[schema_id])) AS [FOREIGN_TABLE_SCHEMA]
, QUOTENAME(po.[name]) AS [FOREIGN_TABLE_NAME]
, QUOTENAME(rccu.[COLUMN_NAME]) AS [FOREIGN_COLUMN_NAME]
, QUOTENAME(SCHEMA_NAME(ro.[schema_id])) AS [PRIMARY_TABLE_SCHEMA]
, QUOTENAME(ro.[name]) AS [PRIMARY_TABLE_NAME]
, QUOTENAME(rc.[name]) AS [PRIMARY_COLUMN_NAME]
, CASE fk.[is_disabled]
WHEN 0 THEN 'CHECK'
ELSE 'NOCHECK'
END AS [Enabled]
FROM
sys.foreign_keys AS fk
JOIN sys.objects AS o ON o.[object_id] = fk.[object_id]
JOIN sys.objects AS po ON po.[object_id] = fk.[parent_object_id]
JOIN sys.objects AS ro ON ro.[object_id] = fk.[referenced_object_id]
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS rccu ON
rccu.[CONSTRAINT_SCHEMA] = SCHEMA_NAME(o.[schema_id])
AND rccu.[CONSTRAINT_NAME] = o.[name]
AND rccu.[TABLE_SCHEMA] = SCHEMA_NAME(po.[schema_id])
AND rccu.[TABLE_NAME] = po.[name]

JOIN sys.index_columns AS ric ON
ric.[object_id] = fk.[referenced_object_id]
AND ric.[index_id] = fk.[key_index_id]
AND ric.[is_included_column] = 0

JOIN sys.columns AS rc ON
rc.[object_id] = fk.[referenced_object_id]
AND rc.[column_id] = ric.[column_id]
WHERE
fk.[is_ms_shipped] = 0
-- AND rc.[collation_name] IS NOT NULL
AND QUOTENAME(po.[name]) = '[TabGroupItems]'


I'm guessing that to fix the problem we want to concatenate the rows for foreign columns into 1 comma separated list of those columns?

I'll be happy to help with testing.

Charlie.
Peso
2009-08-20		 re: Change collation
Yes, there are some issues left to fix. One of them is composite keys as you describe above.
Another is the index thing.

Thank you for your offer. I will continue to update this blog post when the script is updated.
Charlie
2009-08-20		 re: Change collation
I think I have the combined keys working.... (not extensively tested). Performance is... bad but that's going to be a secondary concern for this..


;WITH base AS (
SELECT
o.[schema_Id] AS [schema_Id]
, QUOTENAME(o.[name]) AS [CONSTRAINT_NAME]
, QUOTENAME(SCHEMA_NAME(po.[schema_id])) AS [FOREIGN_TABLE_SCHEMA]
, QUOTENAME(po.[name]) AS [FOREIGN_TABLE_NAME]
, QUOTENAME(rccu.[COLUMN_NAME]) AS [FOREIGN_COLUMN_NAME]
, QUOTENAME(SCHEMA_NAME(ro.[schema_id])) AS [PRIMARY_TABLE_SCHEMA]
, QUOTENAME(ro.[name]) AS [PRIMARY_TABLE_NAME]
, QUOTENAME(rc.[name]) AS [PRIMARY_COLUMN_NAME]
, CASE fk.[is_disabled]
WHEN 0 THEN 'CHECK'
ELSE 'NOCHECK'
END AS [Enabled]
FROM
sys.foreign_keys AS fk
JOIN sys.objects AS o ON o.[object_id] = fk.[object_id]
JOIN sys.objects AS po ON po.[object_id] = fk.[parent_object_id]
JOIN sys.objects AS ro ON ro.[object_id] = fk.[referenced_object_id]

JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS rccu ON
rccu.[CONSTRAINT_SCHEMA] = SCHEMA_NAME(o.[schema_id])
AND rccu.[CONSTRAINT_NAME] = o.[name]
AND rccu.[TABLE_SCHEMA] = SCHEMA_NAME(po.[schema_id])
AND rccu.[TABLE_NAME] = po.[name]

JOIN sys.index_columns AS ric ON
ric.[object_id] = fk.[referenced_object_id]
AND ric.[index_id] = fk.[key_index_id]
AND ric.[is_included_column] = 0

JOIN sys.columns AS rc ON
rc.[object_id] = fk.[referenced_object_id]
AND rc.[column_id] = ric.[column_id]
WHERE
fk.[is_ms_shipped] = 0
-- AND rc.[collation_name] IS NOT NULL
-- AND QUOTENAME(po.[name]) = '[TabGroupItems]'
)
SELECT
ROW_NUMBER() OVER (ORDER BY [schema_id]) AS [RowID]
, [CONSTRAINT_NAME]
, [FOREIGN_TABLE_SCHEMA]
, [FOREIGN_TABLE_NAME]
, LEFT([FOREIGN_COLUMN_NAMES], LEN([FOREIGN_COLUMN_NAMES]) -1) AS [FOREIGN_COLUMN_NAMES]
, [PRIMARY_TABLE_SCHEMA]
, [PRIMARY_TABLE_NAME]
, LEFT([PRIMARY_COLUMN_NAMES], LEN([PRIMARY_COLUMN_NAMES]) -1) AS [PRIMARY_COLUMN_NAMES]
, [Enabled]
FROM
(
SELECT DISTINCT
b.[schema_Id]
, b.[CONSTRAINT_NAME]
, b.[FOREIGN_TABLE_SCHEMA]
, b.[FOREIGN_TABLE_NAME]
, b.[PRIMARY_TABLE_SCHEMA]
, b.[PRIMARY_TABLE_NAME]
, b.[Enabled]

, ( SELECT DISTINCT [FOREIGN_COLUMN_NAME] + ', '
FROM base fc
WHERE
b.[schema_Id] = fc.[schema_Id]
AND b.[CONSTRAINT_NAME] = fc.[CONSTRAINT_NAME]
AND b.[FOREIGN_TABLE_SCHEMA] = fc.[FOREIGN_TABLE_SCHEMA]
AND b.[FOREIGN_TABLE_NAME] = fc.[FOREIGN_TABLE_NAME]
AND b.[PRIMARY_TABLE_SCHEMA] = fc.[PRIMARY_TABLE_SCHEMA]
AND b.[PRIMARY_TABLE_NAME] = fc.[PRIMARY_TABLE_NAME]
AND b.[Enabled] = fc.[Enabled]
FOR XML PATH ('')
)
AS [FOREIGN_COLUMN_NAMES]

, ( SELECT DISTINCT [PRIMARY_COLUMN_NAME] + ', '
FROM base pcn
WHERE
b.[schema_Id] = pcn.[schema_Id]
AND b.[CONSTRAINT_NAME] = pcn.[CONSTRAINT_NAME]
AND b.[FOREIGN_TABLE_SCHEMA] = pcn.[FOREIGN_TABLE_SCHEMA]
AND b.[FOREIGN_TABLE_NAME] = pcn.[FOREIGN_TABLE_NAME]
AND b.[PRIMARY_TABLE_SCHEMA] = pcn.[PRIMARY_TABLE_SCHEMA]
AND b.[PRIMARY_TABLE_NAME] = pcn.[PRIMARY_TABLE_NAME]
AND b.[Enabled] = pcn.[Enabled]
FOR XML PATH ('')
)
AS [PRIMARY_COLUMN_NAMES]
FROM
base b
)
comb

Charlie