• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1243
  • Last Modified:

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

  • 3
  • 2
  • 2
2 Solutions
Thandava VallepalliCommented:

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


sp_unbindefault 'YourTable.YourColumn'

to get rid of the default.
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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.
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.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now