SQL Server 2005 drop column with constraints

I have a column with a "DEFAULT" constraint. I'd like to create a script that drops that column.

The problem is that it returns this error:

Msg 5074, Level 16, State 1, Line 1  
The object 'DF__PeriodSce__IsClo__4BCC3ABA' is dependent on column 'IsClosed'. 
Msg 4922, Level 16, State 9, Line 1 
ALTER TABLE DROP COLUMN IsClosed failed because one or more objects access this column.

I couldn't find an easy way to drop a column and all its associated constraints (only found big scripts that look into the system table... there MUST (!!) be a "nice" way to do it.)

And as the DEFAULT constraint's name has been randomly generated, I can't drop it by name.


Update : The constraint type is "DEFAULT".

I saw the solutions that you all proposed but I find them all really "dirty"... Don't you think? I don't know if it's with Oracle or MySQL but it's possible to do something like:

DROP COLUMN xxx CASCADE CONSTRAINTS 

And it drops all related constraints... Or at least it automatically drops the constraints mapped to that column (at least CHECK constraints!)

Is there nothing like that in MSSQL?

Answers


This query finds default constraints for a given table. It aint pretty, I agree:

select 
    col.name, 
    col.column_id, 
    col.default_object_id, 
    OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst, 
    dobj.name as def_name
from sys.columns col 
    left outer join sys.objects dobj 
        on dobj.object_id = col.default_object_id and dobj.type = 'D' 
where col.object_id = object_id(N'dbo.test') 
and dobj.name is not null

[EDIT] Updated per Julien N's comment


Here is a script that will delete the column along with its default constraint. Replace MYTABLENAME and MYCOLUMNNAME appropriately.

declare @constraint_name sysname, @sql nvarchar(max)

select @constraint_name = name 
from sys.default_constraints 
where parent_object_id = object_id('MYTABLENAME')
AND type = 'D'
AND parent_column_id = (
    select column_id 
    from sys.columns 
    where object_id = object_id('MYTABLENAME')
    and name = 'MYCOLUMNNAME'
    )

set @sql = N'alter table MYTABLENAME drop constraint ' + @constraint_name
exec sp_executesql @sql

alter table MYTABLENAME drop column MYCOLUMNNAME

go

Need Your Help

Using a 'lookup' table in MS-ACCESS for an update query

sql vba ms-access access-vba ms-access-2010

I have an Access Database with a table [tblManipulate] with the following four fields populated with data:

How can drag and drop be achieved with Javascript and Wicket 6?

java javascript tree drag-and-drop wicket-6

I have an inventory application in written in Apache Wicket 6, in which I want to add a drag and drop feature in the organisation tree structure which shows the taxonomy of the organisation. So, with

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.