Altering the collation at a database level

• RedDoorSQL

  Ten Centuries

  Points: 1294

  More actions

  March 13, 2012 at 9:38 am

  #252443

  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.

  🙂

• Ant-Green

  SSC Guru

  Points: 113499

  More actions

  March 13, 2012 at 9:51 am

  #1458597

  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?

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 13, 2012 at 10:50 am

  #1458662

  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!

• RedDoorSQL

  Ten Centuries

  Points: 1294

  More actions

  March 13, 2012 at 1:59 pm

  #1458824

  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.