Solved

Drop a default when dropping a column

Posted on 2004-08-04
7
1,227 Views
Last Modified: 2012-08-13
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
Comment
Question by:plq
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 14

Assisted Solution

by:Thandava Vallepalli
Thandava Vallepalli earned 250 total points
ID: 11714150
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
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 11714158
so here is your dml statements


1. alter table drop constraint <defult_val_constraint_name>

2. alter table drop column

itsvtk
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11714165
Use

sp_unbindefault 'YourTable.YourColumn'

to get rid of the default.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 9

Accepted Solution

by:
crescendo earned 250 total points
ID: 11714175
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
 
LVL 8

Author Comment

by:plq
ID: 11714186
Yes I wouldn't know the name of the default - I'll give it a try

thanks
0
 
LVL 8

Author Comment

by:plq
ID: 11714602
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
 
LVL 8

Author Comment

by:plq
ID: 11715195
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question