在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]。然后你就可以去掉约束了。
其他回答
扩展解决方案(考虑表模式):
-- Drop default contstraint for SchemaName.TableName.ColumnName
DECLARE @schema_name NVARCHAR(256)
DECLARE @table_name NVARCHAR(256)
DECLARE @col_name NVARCHAR(256)
DECLARE @Command NVARCHAR(1000)
set @schema_name = N'SchemaName'
set @table_name = N'TableName'
set @col_name = N'ColumnName'
SELECT @Command = 'ALTER TABLE [' + @schema_name + '].[' + @table_name + '] DROP CONSTRAINT ' + d.name
FROM sys.tables t
JOIN sys.default_constraints d
ON d.parent_object_id = t.object_id
JOIN sys.schemas s
ON s.schema_id = t.schema_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 s.name = @schema_name
AND c.name = @col_name
EXECUTE (@Command)
下面的解决方案将从表中删除列的特定默认约束
Declare @Const NVARCHAR(256)
SET @Const = (
SELECT TOP 1 'ALTER TABLE' + YOUR TABLE NAME +' DROP CONSTRAINT '+name
FROM Sys.default_constraints A
JOIN sysconstraints B on A.parent_object_id = B.id
WHERE id = OBJECT_ID('YOUR TABLE NAME')
AND COL_NAME(id, colid)='COLUMN NAME'
AND OBJECTPROPERTY(constid,'IsDefaultCnst')=1
)
EXEC (@Const)
我发现这是有效的，没有使用连接:
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]则不会返回任何内容。
这将检查外键是否存在。如果它存在，那就放弃它。
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
删除多个列的约束:
declare @table_name nvarchar(256)
declare @Command nvarchar(max) = ''
set @table_name = N'ATableName'
select @Command = @Command + 'ALTER TABLE ' + @table_name + ' drop constraint ' + d.name + CHAR(10)+ CHAR(13)
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 in ('column1','column2','column3')
--print @Command
execute (@Command)
