Cannot alter a column what should i do to alter

Question

Post reply

Cannot alter a column what should i do to alter

SQLTestUser

Hall of Fame

Points: 3997
More actions
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

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

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

crazy4sql SSCoach Points: 19590 More actions · Answer 1

try with wizard.

the column might be referred in some FK.

----------
Ashish

Derrick Smith SSChampion Points: 10833 More actions · Answer 2

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.

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 3

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

crazy4sql SSCoach Points: 19590 More actions · Answer 4

check the dependency of the table using :-

EXEC sp_depends @objname = 'tablename'

----------
Ashish

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

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