SQL Server 2005 drop column with constraints

Question

Hi !

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 is returns me that 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 system table... there MUST (!!) be a "nice" way to do that !).

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

Thanks for your help.

---

Update :
The constraint type is "DEFAULT".

I found the solutions you propose but I find it really "dirty"... Don't you think ? I don't know if its 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 !)

There is nothing like that in MSSQL ?

Answer 1

What do you mean randomly generated? You can look up the constraints on the specific column in management studio or via the sys.tables view and find what the name(s) are.

Then, you can change your script to drop the constraints prior to dropping the column. What type of constraint is this? If it is a foreign key constraint, make sure that doing this won't hurt the data integrity within you database.

Answer 2

You can get the constraint names by querying the information_schema system views.

select CONSTRAINT_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = '<tablename>' AND COLUMN_NAME = 'IsClosed'

Answer 3

I believe explicitly dropping the constraints prior to dropping the column is a "cleaner" solution. This way, you don't drop constraints you may not be aware of. If the drop still fails, you know there are additional constraints remaining. I like being in control of exactly what is happening to my database.

Plus, scripting the drops explicitly guarantees the script and hopefully the results to be repeatable in exactly the way you intend.

Answer 4

> select CONSTRAINT_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
> WHERE TABLE_NAME = '<tablename>' AND COLUMN_NAME = 'IsClosed'

It's not the right solution as it is explained here : http://msdn.microsoft.com/en-us/library/aa175912.aspx that :

Unfortunately, the name of the column default constraint isn't kept in the ANSI COLUMNS view, so you must go back to the system tables to find the name

The only way I found to get the name of the DEFAULT constraint is this request :

select  
    t_obj.name     as TABLE_NAME
    ,c_obj.name    as CONSTRAINT_NAME
    ,col.name    as COLUMN_NAME

from    sysobjects c_obj
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.xtype = 'D'

Am I the only one to find it crazy to be unable to delete easily a constraint that only concerns the columns I'm trying to drop ?
I need to execute a request with 3 joins just to get the name...

Answer 5

Just Generate Scripts for the table. There you can find the name of all constraints.

Answer 6

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

Answer 7

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

declare @default sysname, @sql nvarchar(max)

select @default = 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 ' + @default
exec sp_executesql @sql

alter table MYTABLENAME drop MYCOLUMNNAME

go

Answer 8

Perhaps it could help a little more:

declare @tablename nvarchar(200)
declare @colname nvarchar(200)
declare @default sysname, @sql nvarchar(max)

set @tablename = 'your table'
set @colname = 'column to drop'

select @default = name 
from sys.default_constraints 
where parent_object_id = object_id(@tablename)
AND type = 'D'
AND parent_column_id = (
    select column_id 
    from sys.columns 
    where object_id = object_id(@tablename)
    and name = @colname 
    )

set @sql = N'alter table ' + @tablename + ' drop constraint ' + @default
exec sp_executesql @sql

set @sql = N'alter table ' + @tablename + ' drop column ' + @colname
exec sp_executesql @sql

Only need to set the @tablename & @colname variables to drop the column.