Cannot alter a column what should i do to alter

  • I ran the command

    Alter Table Login ALter Column X varchar NULL ;

    and got

    Msg 5074, Level 16, State 1, Line 30

    The index 'X' is dependent on column 'X' ( dont understand this when there are no index).

    Msg 4922, Level 16, State 9, Line 30

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

    What can i do to alter this column

  • try with wizard.

    the column might be referred in some FK.

    ----------
    Ashish

  • Do you have any views pointing to this table?

    Check the dependencies through the SSMS gui and see what relies on it (right click -> view dependencies). You'll find the culprit there, most likely.

  • i can do that when i am on my SSMS but i have to send out a script to the client, is there any way i can turn the indexes off when i run the script and turn them back on after the script is executed

    would if exist do it ?? but how do i know the index name if it was automatically built by the SQL server, some coolumns have constraints that are placed there by SQL server and i have no idea what to do about them.

    anywyas for now all i am concerned about it dropin the index and then reapplying it after i change the column name

  • check the dependency of the table using :-

    EXEC sp_depends @objname = 'tablename'

    ----------
    Ashish

  • avi-631555 (9/22/2010)


    i can do that when i am on my SSMS but i have to send out a script to the client, is there any way i can turn the indexes off when i run the script and turn them back on after the script is executed

    would if exist do it ?? but how do i know the index name if it was automatically built by the SQL server, some coolumns have constraints that are placed there by SQL server and i have no idea what to do about them.

    anywyas for now all i am concerned about it dropin the index and then reapplying it after i change the column name

    A couple of options:

    1. In SSMS you can have the change script created for you instead of making the change through the GUI. Delete the column from the table in the designer (don't save), and then under the table designer menu there is a Generate Change Script option.

    2. You can write a script that finds the indexes that include that column using sys.columns, sys.indexes and sys.index_columns and generate DROP statements for those indexes. This script should get you started.

    SELECT

    I.name AS index_name,

    C.name AS column_name,

    IC.is_included_column

    FROM

    sys.indexes AS I JOIN

    sys.index_columns AS IC

    ON I.index_id = IC.index_id AND

    I.object_id = IC.object_id JOIN

    sys.columns AS C ON IC.column_id = C.column_id AND IC.object_id = C.object_id

    WHERE

    I.object_id = OBJECT_ID('table name') AND

    C.name = 'Column Name'

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

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