alter column without drop index

[pmgatwork]

Hi all
I want to be able to alter a column name from null to not null which i can do using the designer but if i try with the following script i cannot do this until i drop an index trhat uses that column. Is this possible from a script

ALTER TABLE brokered alter column site_id numeric(2,0)not null
returns
The index 'BROKERED_BROKERED' is dependent on column 'site_id'.


many thanks
Peter

 

[ptheriault]

It works in the GUI because it alters that table by creating a new table. In other words. It creates a new table, moves the data, adds any index, triggers, then drops the old table and renames the new one to the old table name.

So to answer your question. You must drop the index first.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

When you want to see what the GUI is actually doing (because all the GUI does is execute T/SQL code against the database) instead of clicking the save icon, click the save icon with the little script on it. This will give you the script which the GUI will execute against the database and show you exactly what it's going to do in the background.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog