如何检查SQL Server约束是否存在？

小编典典

如何检查SQL Server约束是否存在？

sql

我有以下几点：

IF OBJECT_ID(N'[dbo].[webpages_Roles_UserProfiles_Target]', 'xxxxx') IS NOT NULL
   DROP CONSTRAINT [dbo].[webpages_Roles_UserProfiles_Target]

我希望能够在删除约束之前检查是否存在约束。我在表中使用上面的代码，类型为“ U”。

我如何修改上面的代码（更改xxxx）以使其检查约束的存在？

阅读 260

2021-04-22

共1个答案

小编典典

SELECT
*
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

否则尝试一下

  SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'

或者

IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'dbo.TableName'))
 BEGIN 
ALTER TABLE TableName DROP CONSTRAINT CONSTRAINTNAME 
END

2021-04-22