sql-servert-sqlsql-server-2005constraints

How to drop a column with constraints in SQL Server 2005


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.

The constraint type is "DEFAULT".

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?


Solution

  • This query finds default constraints for a given table. It ain't 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