Post reply

Cannot alter a column what should i do to alter

  • September 22, 2010 at 10:31 am

    #240819

    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

  • September 22, 2010 at 10:43 am

    #1225760

    try with wizard.

    the column might be referred in some FK.

    ----------
    Ashish

  • September 22, 2010 at 10:47 am

    #1225763

    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.

  • September 22, 2010 at 10:59 am

    #1225773

    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

  • September 22, 2010 at 11:05 am

    #1225776

    check the dependency of the table using :-

    EXEC sp_depends @objname = 'tablename'

    ----------
    Ashish

  • September 23, 2010 at 6:29 am

    #1226264

    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'

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

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

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