在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'，但如果它找不到它，不要给出任何错误。
当前回答
适用于创建了多个默认约束或检查约束的列:
修改https://stackoverflow.com/a/16359095/206730脚本
注意:此脚本用于sys.check_constraints
declare @table_name nvarchar(128)
declare @column_name nvarchar(128)
declare @constraint_name nvarchar(128)
declare @constraint_definition nvarchar(512)
declare @df_name nvarchar(128)
declare @cmd nvarchar(128)
PRINT 'DROP CONSTRAINT [Roles2016.UsersCRM].Estado'
declare constraints cursor for
select t.name TableName, c.name ColumnName, d.name ConstraintName, d.definition ConstraintDefinition
from sys.tables t
join sys.check_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 = N'Roles2016.UsersCRM' and c.name = N'Estado'
open constraints
fetch next from constraints into @table_name , @column_name, @constraint_name, @constraint_definition
while @@fetch_status = 0
BEGIN
print 'CONSTRAINT: ' + @constraint_name
select @cmd = 'ALTER TABLE [' + @table_name + '] DROP CONSTRAINT [' + @constraint_name + ']'
print @cmd
EXEC sp_executeSQL @cmd;
fetch next from constraints into @table_name , @column_name, @constraint_name, @constraint_definition
END
close constraints
deallocate constraints
其他回答
删除数据库安全的nvarchar(max)阈值中的所有默认约束。
/* WARNING: THE SAMPLE BELOW; DROPS ALL THE DEFAULT CONSTRAINTS IN A DATABASE */
/* MAY 03, 2013 - BY WISEROOT */
declare @table_name nvarchar(128)
declare @column_name nvarchar(128)
declare @df_name nvarchar(128)
declare @cmd nvarchar(128)
declare table_names cursor for
SELECT t.name TableName, c.name ColumnName
FROM sys.columns c INNER JOIN
sys.tables t ON c.object_id = t.object_id INNER JOIN
sys.schemas s ON t.schema_id = s.schema_id
ORDER BY T.name, c.name
open table_names
fetch next from table_names into @table_name , @column_name
while @@fetch_status = 0
BEGIN
if exists (SELECT top(1) d.name 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 = @column_name)
BEGIN
SET @df_name = (SELECT top(1) d.name 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 = @column_name)
select @cmd = 'ALTER TABLE [' + @table_name + '] DROP CONSTRAINT [' + @df_name + ']'
print @cmd
EXEC sp_executeSQL @cmd;
END
fetch next from table_names into @table_name , @column_name
END
close table_names
deallocate table_names
这将检查外键是否存在。如果它存在，那就放弃它。
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
我有一些列创建了多个默认约束，所以我创建了以下存储过程:
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
我发现这是有效的，没有使用连接:
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]则不会返回任何内容。
适用于创建了多个默认约束或检查约束的列:
修改https://stackoverflow.com/a/16359095/206730脚本
注意:此脚本用于sys.check_constraints
declare @table_name nvarchar(128)
declare @column_name nvarchar(128)
declare @constraint_name nvarchar(128)
declare @constraint_definition nvarchar(512)
declare @df_name nvarchar(128)
declare @cmd nvarchar(128)
PRINT 'DROP CONSTRAINT [Roles2016.UsersCRM].Estado'
declare constraints cursor for
select t.name TableName, c.name ColumnName, d.name ConstraintName, d.definition ConstraintDefinition
from sys.tables t
join sys.check_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 = N'Roles2016.UsersCRM' and c.name = N'Estado'
open constraints
fetch next from constraints into @table_name , @column_name, @constraint_name, @constraint_definition
while @@fetch_status = 0
BEGIN
print 'CONSTRAINT: ' + @constraint_name
select @cmd = 'ALTER TABLE [' + @table_name + '] DROP CONSTRAINT [' + @constraint_name + ']'
print @cmd
EXEC sp_executeSQL @cmd;
fetch next from constraints into @table_name , @column_name, @constraint_name, @constraint_definition
END
close constraints
deallocate constraints
