Post reply

Getting error in Changing Collation of Database Column

  • October 12, 2011 at 12:34 am

    #249862

    ALTER TABLE mit_server

    ALTER COLUMN server_name VARCHAR(255)

    COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

    Error is

    Msg 5074, Level 16, State 1, Line 1

    The index 'server_name_ind' is dependent on column 'server_name'.

    Msg 4922, Level 16, State 9, Line 1

    ALTER TABLE ALTER COLUMN server_name failed because one or more objects access this column.

    Thanks

    Kumar

  • October 12, 2011 at 2:48 am

    #1393294

    In order to change the collation (or any other property) of a column, all dependant objects must be detached.

    This means indexes, defaults and the like must be dropped and recreated.

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • October 12, 2011 at 3:00 am

    #1393301

    Thanks for your reply.

    It is very useful. But how could i know what are the indexes and constraints on column..?

    and second is some one has created the indexes and constraints on that column. so is there any way to get the indexes so that i can detach and recreate the indexes ?

    please write in detail.. 🙂

    Thanks

    Kumar

  • October 12, 2011 at 3:27 am

    #1393317

    This extracts the indexes that depend on the column.

    SELECT A.name

    FROM sys.indexes AS A

    INNER JOIN sys.index_columns AS B

    ON A.object_id = B.object_id

    AND A.index_id = B.index_id

    INNER JOIN sys.tables AS T

    ON T.object_id = A.object_id

    INNER JOIN sys.columns AS C

    ON T.object_id = C.object_id

    AND B.index_column_id = C.column_id

    WHERE T.name = 'yourTableName'

    AND C.name = 'yourColumnName'

    You can select the index on the object explorer in SSMS, right click and select "Script index as create to... new query editor window".

    Once you dropped the index and modified the column, you can run the script to re-create the index.

    -- Gianluca Sartori

  • October 12, 2011 at 4:12 am

    #1393329

    Thanks for the Great Info 🙂

    I resolved my issue by recreating the Index.

    However the above query which you have written is not working. it is not returning the index.

    Thanks

    kumar

  • October 12, 2011 at 4:21 am

    #1393334

    kumarreddy72 (10/12/2011)

    Thanks for the Great Info 🙂

    I resolved my issue by recreating the Index.

    However the above query which you have written is not working. it is not returning the index.

    Thanks

    kumar

    Weird. It works for me.

    Anyway, glad you sorted it out.

    -- Gianluca Sartori

Viewing 6 posts - 1 through 6 (of 6 total)

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