Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1238
  • 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

thanks
0
plq
Asked:
plq
  • 3
  • 2
  • 2
2 Solutions
 
Thandava VallepalliCommented:
Hi,

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.


itsvtk
0
 
Thandava VallepalliCommented:
so here is your dml statements


1. alter table drop constraint <defult_val_constraint_name>

2. alter table drop column

itsvtk
0
 
crescendoCommented:
Use

sp_unbindefault 'YourTable.YourColumn'

to get rid of the default.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
crescendoCommented:
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.
0
 
plqAuthor Commented:
Yes I wouldn't know the name of the default - I'll give it a try

thanks
0
 
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
0
 
plqAuthor Commented:
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"




0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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