Collation of Existing Data

  • ashley 87893

    Grasshopper

    Points: 17

    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***.

  • crow1969

    SSCrazy

    Points: 2732

    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

    Grasshopper

    Points: 17

    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'

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

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