Post reply

update syscolumns to change collation?

  • March 29, 2006 at 7:23 am

    #113354

    Posted on behalf of a team mate...

    "In order to change the collation of a column within a table, is it possible to directly update the syscolumns table within the database without adversely affecting the database or any queries using that database?   Although we know that there is a SQL command, Alter Table, which will allow you to make changes to the collation of a column, this command has restrictions in that it can not affect columns which have constraints such as Primary Keys or Indices. In order to use Alter Table to perform the desired operation, one would have to remove such constraints before changing the collation only to replace them later. This is very long and painstaking task.

    I have tested and shown that it is possible to update the syscolumns table, replacing the collationid field therein with the desired collation id. I have also observed that MS SQL will automatically recalculate all dependant columns to the collationid column within syscolumns. I have also shown that doing this will resolve collation issues between two databases through execution of various text queries.

    My question is not if it is possible to update any syscolumns table with any collation I want it to be (as I already know that this is possible), my question is that if in doing so I may end up breaking something that I may not currently be able to forsee."

  • March 29, 2006 at 9:26 am

    #629538

    I would not recommend doing that.

    First, you don't know how Microsoft programmers coded changing collation

    Second, you may change data by changing collation

    I would recommend to create another column withthe correct collation and pump data. Keep the old column for the reference.

    OR (if other fields don't contain a lot if data):

    You can script and re-create the whole table (1  min of work) and change the collation for that field in the script before running it. Than use DTS to pump data from your table to the new table. Than rename and keep the old table for the reference.

    Regards,Yelena Varsha

  • March 29, 2006 at 9:32 am

    #629541

    I agree with Yelena. You're playing with fire here.

  • March 29, 2006 at 1:59 pm

    #629652

    Thanks for those two very valid points, Yelena.

    We have already explored your recommended method and the sheer size of that undertaking (70+ databases, some with as many as 1,400 tables) sent us looking for any better solution.  

    Its sort of like deciding whether to take a new wonder drug... it may well kill you as quick as the horrible illness it was intended to cure...

     

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

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