Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    62

    Unanswered: Changing Database Collation setting

    Hi,

    I currently have a server with the collation set to SQL_Latin1_General_CP1_CI_AS.

    However, some of the databases within the server are set to Latin1_General_BIN, probably because they were restored from another server some time ago. Also, even within the databases that have Latin1_General_BIN, some of the columns are set to SQL_Latin1_General_CP1_CI_AS, very confusing to say the least.

    What i would like to do is change the database collation settings for these databases to match the server setting. I would also like to change all of the tables within these databases to have the columns also set to the server collation settings.

    Is there a good way to go about this? I'm looking for the steps that i would need to take to make sure i don't mess anything up as these databases have there own sets of views and sp's that run each day.

    Thanks
    Scott

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    1) Make sure you have good backups
    2) Get downtime approved. You don't want users tripping you up
    3) Test your backups, to make sure they work
    4) Get scripts for each tables' indexes, unique constraints, primary keys, and foreign keys
    5) Make sure that any unique or primary keys will still be unique after the change.
    6) Check the backups again, to make sure they do really really work
    7) Drop constraints, and indexes on one table (may require dropping foreign keys on other tables)
    8) alter each column to the desired collation
    9) rebuild any indexes/constraints dropped in step 7.
    10) Repeat for all tables.
    11) alter the database collation with the ALTER DATABASE statement.
    12) Check in your code for any reference to the COLLATE statement (these may need to be removed).
    13) Hug your backups (they deserved it)
    EDIT: 14) Update index statistics for all tables.

    I probably left out a thing or two. But you should really be certain that whatever you do, you can get back to where you started. Try this out with one of the databases in a test environment first, so you get a feel for what will go wrong.

  3. #3
    Join Date
    Sep 2009
    Posts
    62
    OK,

    as far as searching all the views/stored procs/functions for the word 'collate', do you happen to have or know of a script that can do that, or is that not possible to search at that level?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
    select object_name(object_id)
    from sys.sql_modules
    where definition like '%collate%'
    It may be SQL_Source, instead of SQL_Moules. I can't be sure. Be sure to collate the string comparison, or you may end up missing things like COLLATE, or Collate, or CoLlAtE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •