在Microsoft SQL Server中,我知道检查一个列是否存在默认约束并删除默认约束的查询是:

IF EXISTS(SELECT * FROM sysconstraints
  WHERE id=OBJECT_ID('SomeTable')
  AND COL_NAME(id,colid)='ColName'
  AND OBJECTPROPERTY(constid, 'IsDefaultCnst')=1)    
ALTER TABLE SomeTable DROP CONSTRAINT DF_SomeTable_ColName

但是由于以前版本的数据库的拼写错误,约束的名称可能是DF_SomeTable_ColName或DF_SmoeTable_ColName。

我如何删除默认约束没有任何SQL错误?默认的约束名称不会显示在INFORMATION_SCHEMA表中,这使得事情变得有点棘手。

因此,类似于'删除这个表/列中的默认约束',或'删除DF_SmoeTable_ColName',但如果它找不到它,不要给出任何错误。


当前回答

使用此命令浏览所有约束:

exec sp_helpconstraint 'mytable' --and look under constraint_name. 

它看起来像这样:DF__Mytable__Column__[ABC123]。然后你就可以去掉约束了。

其他回答

我发现这是有效的,没有使用连接:

DECLARE @ObjectName NVARCHAR(100)
SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS
WHERE [object_id] = OBJECT_ID('[tableSchema].[tableName]') AND [name] = 'columnName';
EXEC('ALTER TABLE [tableSchema].[tableName] DROP CONSTRAINT ' + @ObjectName)

只需确保columnName周围没有括号,因为查询正在寻找精确匹配,如果它是[columnName]则不会返回任何内容。

我有一些列创建了多个默认约束,所以我创建了以下存储过程:

CREATE PROCEDURE [dbo].[RemoveDefaultConstraints] @table_name nvarchar(256), @column_name nvarchar(256)
AS
BEGIN

    DECLARE @ObjectName NVARCHAR(100)

    START: --Start of loop
    SELECT 
        @ObjectName = OBJECT_NAME([default_object_id]) 
    FROM 
        SYS.COLUMNS
    WHERE 
        [object_id] = OBJECT_ID(@table_name) 
        AND [name] = @column_name;

    -- Don't drop the constraint unless it exists
    IF @ObjectName IS NOT NULL
    BEGIN
        EXEC ('ALTER TABLE '+@table_name+' DROP CONSTRAINT ' + @ObjectName)
        GOTO START; --Used to loop in case of multiple default constraints
    END
END
GO

-- How to run the stored proc.  This removes the default constraint(s) for the enabled column on the User table.
EXEC [dbo].[RemoveDefaultConstraints] N'[dbo].[User]', N'enabled'
GO

-- If you hate the proc, just get rid of it
DROP PROCEDURE [dbo].[RemoveDefaultConstraints]
GO

Rob Farley的博客文章可能会有所帮助:

有两种方法可以在不知道默认约束名称的情况下查找/删除它

喜欢的东西:

 declare @table_name nvarchar(256)
 declare @col_name nvarchar(256)
 set @table_name = N'Department'
 set @col_name = N'ModifiedDate'

 select t.name, c.name, d.name, d.definition
 from 
     sys.tables t
     join sys.default_constraints d on d.parent_object_id = t.object_id
     join sys.columns c on c.object_id = t.object_id
                           and c.column_id = d.parent_column_id
 where 
     t.name = @table_name
     and c.name = @col_name
declare @ery nvarchar(max)
declare @tab nvarchar(max) = 'myTable'
declare @qu nvarchar(max) = 'alter table '+@tab+' drop constraint '

select @ery = (select bj.name from sys.tables as tb 
inner join sys.objects as bj 
on tb.object_id = bj.parent_object_id
where tb.name = @tab and bj.type = 'PK')

exec(@qu+@ery)

来看看。

这将检查外键是否存在。如果它存在,那就放弃它。

DECLARE @SCHEMA_NAME NVARCHAR(256)
-- The table name you what drop the foreign key from.
DECLARE @ALTER_TABLE_NAME NVARCHAR(256)
-- The table name is liked with the foreign key.
DECLARE @REF_TABLE_NAME NVARCHAR(256) 
DECLARE @COMMAND  NVARCHAR(MAX)

SET @SCHEMA_NAME = N'MySchema';
SET @ALTER_TABLE_NAME = N'MyAlterTable';
SET @REF_TABLE_NAME = N'MyReferTable';

IF EXISTS (
    SELECT NAME
    FROM SYS.FOREIGN_KEYS
    WHERE PARENT_OBJECT_ID = (
        SELECT OBJECT_ID
        FROM SYS.OBJECTS
        WHERE OBJECT_ID = OBJECT_ID(@ALTER_TABLE_NAME)
    )
    AND REFERENCED_OBJECT_ID = (
        SELECT OBJECT_ID
        FROM SYS.OBJECTS
        WHERE OBJECT_ID = OBJECT_ID(@REF_TABLE_NAME)
    )
)
BEGIN
    SELECT @COMMAND = 'ALTER TABLE ['
        + @SCHEMA_NAME
        + '].['
        + @ALTER_TABLE_NAME
        + '] DROP CONSTRAINT '
        + NAME
    FROM SYS.FOREIGN_KEYS
    WHERE PARENT_OBJECT_ID = (
        SELECT OBJECT_ID
        FROM SYS.OBJECTS
        WHERE OBJECT_ID = OBJECT_ID(@ALTER_TABLE_NAME)
    )
    AND REFERENCED_OBJECT_ID = (
        SELECT OBJECT_ID
        FROM SYS.OBJECTS
        WHERE OBJECT_ID = OBJECT_ID(@REF_TABLE_NAME)
    )
    
    EXECUTE (@COMMAND)
END
GO