Altering the collation at a database level

  • We have a problem with a database (CRM) was installed through a third party supplier, they've installed the database with Latin1_General_CI_AI, However the server collation is set as Latin1_General_CI_AS which is in-line with the rest of our SQL Server estate.

    After much research I'm trying to compile a list of what's needed to alter the database so the collation is fully converted.

    I know if I alter the database collation it will create any object which is created from that point onwards in the new (correct) collation but I need to convert the current objects etc as well.

    I believe I need to:

    Convert any table with Text,Char,Varchar,Nvarchar,Nchar columns to have the new collation.

    re-build all indexes on the database.

    re-created any stored proc or function which has specifically specifies collate as part of the t-sql.

    Can anyone help me with any suggestion?, I do believe there is an undocumented function but my company are not willing to use this method, and rightly so.

    🙂

  • what is the backing behind changing the collation? is it just to have the same collation throughout or is there other factors at play here?

  • no tool, documented or not that i ever saw to do this;

    i've had to script this out manually, which is a bitc...er i mean .. rather involved.

    here's my old mental map for it:

    --Brainstorm: first map out a list of all the objects to change;

    --simple constraints

    --STEP_001 check constriants

    --STEP_002 default constraints

    --STEP_003 calculated column definitions

    --STEP_004 foreign key constraints

    --complex constraints and indexes (unique/pk/regular/includes/filtered indexes)

    --STEP_005 primary keys

    --STEP_006 unique indexes

    --STEP_007 regular indexes(also featuring includes or filtered indexes)

    --columns themselves

    --STEP_008 Column Collation definitions

    --views that reference any of the object tables

    --STEP_009 refresh dependent views, procs and functions

    I know my unfinished script was over 600 lines long before i got distracted with other shiny things...it's a lot of work.

    I know I've googled and saw other script contributions out there, but the few i tried were not robust enough for me.

    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!

  • Because we have another database on the same server which both constantly communicate and both of them are of a different collation therefore queries are taking extra time and any other queries need the collate database_default adding

    just annoying really.

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

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