SQL Server 2005 删除带有约束的列 [英] SQL Server 2005 drop column with constraints

问题描述

我有一列带有DEFAULT"约束.我想创建一个删除该列的脚本.

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.)

由于 DEFAULT 约束的名称是随机生成的，我不能按名称删除它.

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

更新:
约束类型为DEFAULT".

Update :
The constraint type is "DEFAULT".

我看到了你们提出的解决方案，但我发现它们都非常肮脏"......你不觉得吗?我不知道是使用 Oracle 还是 MySQL，但可以执行以下操作:

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!)

MSSQL 中没有类似的东西吗?

Is there nothing like that in MSSQL?

推荐答案

此查询查找给定表的默认约束.它不漂亮，我同意:

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

根据 Julien N 的评论更新

Updated per Julien N's comment

这篇关于SQL Server 2005 删除带有约束的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！