Errors executing ALTER TABLE table DROP COLUMN [column]

Question

Well I already looked at themes alike my current problem but nothing seems to fit perfectly .. so a new post:

i have a rather long sql-script, from exporting with phpmyadmin and use it to migrate the database to SQL-Server Express 2008 R2 - I change column definitions where needed and everything works fine. But the last table is created from another table (insert ... select) and after that I want to remove the now unnecessary columns from the "source"-table

But then :

Msg 5074, Level 16, State 1, Line 1
The object 'DF__tickets__flag__5F141958' is dependent on column 'flag'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN flag failed because one or more objects access this column.

Exact Syntax:

ALTER

TABLE tickets DROP COLUMN flag;
commit;

 

I can easily drop the columns using SQL Server Management Studio (Express) - but I need to do it with the script since on the final server there wont be a management suite

So what can I do to resolve it - I guess that the "DF_...." object is created dynamically, so next time I do the import  it will get a new name .. is there some kind of SET-Statement that lets me drop the column like it works in other databases?

thanks for your support

First : I didnt create any views and the column doesnt have any real constraints aside from a simple  default value (create statement says: flag integer default NULL, )

Answer 1

Hello,

Please, could you give the complete definition of your table ?

I am thinking that the DF_... object is a default constraint on the column you are dropping

Some links about default constraint:

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.defaultrulebase.drop(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms162134(v=SQL.100).aspx

Pay attention : you have  types for the defaults :

- the 1st is the default for the whole datebase ( the 2 previous links are for this kind of default )

- the 2nd one is for the default constraint on a particular column of a given table

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.column_members(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.column.defaultconstraint(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.defaultconstraint(v=SQL.100).aspx

to drop this constrant, you have to use the Drop method of the DefaultConstraint class

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.defaultconstraint.drop(v=SQL.100).aspx

just afer having called your Drop() method, it is better to call the Refresh method for the Column object, followed by a call to the Refresh() method on the Table object after the Drop of the Column object ( i prefer to multiply the calls to Refresh() on the parent object after each add, modification or drop on a child object ).

Don't hesitate to post again for more help or explanations.

I think that , in your case , that you have to try the DefaultConstraint object.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.