Results 1 to 3 of 3
  1. #1
    Join Date
    May 2005

    Unanswered: SQL Server 2005 changing DB collation


    I have a DB which has its collation set to SQL_Latin1_General_CP1_CI_AS and i need to change it to Latin1_General_CI_AS,

    does anyone have any idea how to do this?

    i have tried right clicking on the DB and changing its collation in the options tab, but this only changes what the default is, so all the old columns still have the old collatin,

    does anyone know how to change this?

  2. #2
    Join Date
    Jul 2003
    I researched this a few weeks ago, but got scared off.

    Search BOL for "Changing Collations". It involves Alter Database,
    and also ALTER TABLE for each column in already built user-defined tables.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jan 2003
    Provided Answers: 17
    If the database is small, you may just want to create a new database with the correct collation, create all the tables in the new database, copy the data over, then rename the old, rename the new, and off you go.

    Alternatively, here is the outline of how to do it in place:

    1) Use ALTER DATABASE to change the database default collation.
    2) For each table
    a) Drop each index that includes a char, varchar, nchar, or nvarchar column
    b) Change the collation of each char, varchar, nchar, or nvarchar column
    with the alter table alter column command.
    c) Re-create all the dropped indexes.

    If you are using SQL 2000, this could line you up for a world of hurt if you use a lot of temp tables. Since you are using SQL 2005, however, you should be all set, as the temp table collation is by default the same as the local database collation.

Posting Permissions

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