Collation of Existing Data

Question

Collation of Existing Data

ashley 87893

Valued Member

Points: 67
More actions
April 23, 2019 at 1:57 pm

#3524469

We've had a server move and a replication database was requested to be setup as a certain collation (SQL_Latin1_General_CP1_CI_AS) which has caused us a whole lot of hassle as all of the other databases are Latin1_General_CI_AS on the server. So we've had to had collate to the SQL server scripts.
Unfortunately the replication database needs to stay as SQL_Latin1_General_CP1_CI_AS so we'll need to go through the task of changing the collation on 8 databases with multiple columns and keys.
What is the best way/best script to go about this?
I've had a Google and it looks like we may need to export the data and import and it involves dropping tables etc.
Where is best to start with this. I'm not a DBA but this just seems to be a cluster f***.

Viewing 3 posts - 1 through 2 (of 2 total)

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

crow1969 Hall of Fame Points: 3001 More actions · Answer 1

Start with this monstrosity. It will generate all of the individual ALTER TABLE statements, but you will want to check them thoroughly. You will need to drop/rebuild any indexes that refer to columns with character data. This may include dropping/rebuilding foreign keys as well. Possibly check constraints as well.

select 'alter table [' + object_schema_name(c.object_id) + '].[' + object_name(c.object_id) + '] alter column [' + c.name + '] ' + t.name + '(' + case when c.max_length = -1 then 'max' else convert(varchar(4), c.max_length/scalingfactor.factor) end + ') ' + case when c.is_nullable = 1 then 'NULL' else 'NOT NULL' end + ' collate SQL_Latin1_general_CP1_CI_AS'

from sys.columns c join

sys.types t on c.user_type_id = t.user_type_id join

(values (167, 1), (175, 1), (231, 2), (239, 2)) scalingfactor (user_type_id, factor) on t.user_type_id = scalingfactor.user_type_id

where objectproperty(c.object_id, 'IsSystemTable') = 0

ashley 87893 Valued Member Points: 67 More actions · Answer 2

Thanks for the reply @crow1969,

I've used your script although, had to tweak it a little. Seems to be ok on a couple of test DB's without any primary/foreign keys etc. I'll be giving it a go on a more complex database tomorrow.

SELECT 'ALTER table
[' + OBJECT_SCHEMA_NAME(c.object_id) + '].[' + OBJECT_NAME(c.object_id) + '] 
ALTER column 
[' + c.name + '] ' + t.name + '(' + CASE
                                            WHEN c.max_length = -1 THEN
                                                'MAX'
                                            ELSE
                                                CONVERT(VARCHAR(4), c.max_length / scalingfactor.factor)
                                        END + ') collate SQL_Latin1_general_CP1_CI_AS ' + CASE
                                                         WHEN c.is_nullable = 1 THEN
                                                             'NULL'
                                                         ELSE
                                                             'NOT NULL'
                                                     END
FROM sys.columns c
    JOIN sys.types t
        ON c.user_type_id = t.user_type_id
    JOIN
    (
        VALUES
            (167, 1),
            (175, 1),
            (231, 2),
            (239, 2)
    ) scalingfactor (user_type_id, factor)
        ON t.user_type_id = scalingfactor.user_type_id
WHERE OBJECTPROPERTY(c.object_id, 'IsSystemTable') = 0
AND OBJECT_SCHEMA_NAME(c.object_id) = 'dbo'
AND c.collation_name != 'SQL_Latin1_General_CP1_CI_AS'