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

Who is Participating?
crescendoConnect With a Mentor Commented:
With sp_unbindefault you don't need to know the name of the default, just the name of the table and column, so it's easier than dropping the default by name.
Thandava VallepalliConnect With a Mentor Commented:

Here is the rule... you have to drop the default constraint first before droping the column...

DROP { [CONSTRAINT] constraint_name | COLUMN column_name }

Specifies that constraint_name or column_name is removed from the table. DROP COLUMN is not allowed if the compatibility level is 65 or earlier. Multiple columns and constraints can be listed. A column cannot be dropped if it is:

A replicated column.

Used in an index.

Used in a CHECK, FOREIGN KEY, UNIQUE, or PRIMARY KEY constraint.

Associated with a default defined with the DEFAULT keyword, or bound to a default object.

Thandava VallepalliCommented:
so here is your dml statements

1. alter table drop constraint <defult_val_constraint_name>

2. alter table drop column

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.


sp_unbindefault 'YourTable.YourColumn'

to get rid of the default.
plqAuthor Commented:
Yes I wouldn't know the name of the default - I'll give it a try

plqAuthor Commented:
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
plqAuthor Commented:
I found this view on the net

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
      c_obj.uid      = user_id()
      and c_obj.xtype      = 'D'


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"

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.