asked on

Drop a default when dropping a column

I'm programmatically allowing users to add and drop columns

Its working OK but when a column has been given a default, the

alter table drop column

command fails because the default constraint exists.

Is there a way to make it ignore defaults and just drop the column and any defaults with it ?

Defaults are never shared between columns in this DB

thanks

SOLUTION

Thandava Vallepalli

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Thandava Vallepalli

so here is your dml statements

1. alter table drop constraint <defult_val_constraint_name>

2. alter table drop column

itsvtk

crescendo

Use

sp_unbindefault 'YourTable.YourColumn'

to get rid of the default.

ASKER CERTIFIED SOLUTION

crescendo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

plq

ASKER

Yes I wouldn't know the name of the default - I'll give it a try

thanks

plq

ASKER

I'm getting this error message

Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
Cannot unbind from 'Asset.spcTestField'. Use ALTER TABLE DROP CONSTRAINT.

Here's the program generated script

set ansi_warnings off
exec sp_unbindefault 'Asset.spcTestField'
alter table Asset drop column spcTestField

plq

ASKER

I found this view on the net

create view v_DEFAULT_CONSTRAINT
as
select      db_name()                        as CONSTRAINT_CATALOG
      ,t_obj.name                         as TABLE_NAME
      ,user_name(c_obj.uid)                  as CONSTRAINT_SCHEMA
      ,c_obj.name                        as CONSTRAINT_NAME
      ,col.name                        as COLUMN_NAME
      ,col.colid                        as ORDINAL_POSITION
      ,com.text                        as DEFAULT_CLAUSE

from      sysobjects      c_obj
join       syscomments      com on       c_obj.id = com.id
join       sysobjects      t_obj on c_obj.parent_obj = t_obj.id
join sysconstraints con on c_obj.id      = con.constid
join       syscolumns      col on t_obj.id = col.id
                  and con.colid = col.colid
where
      c_obj.uid      = user_id()
      and c_obj.xtype      = 'D'

GO

That enabled me to select the name of the default based on table and column name, and then I could drop the default using "alter table drop constraint"