如何检查Sql server中是否存在约束? [英] How to check if a Constraint exists in Sql server?

问题描述

我有这个 sql:

ALTER TABLE dbo.ChannelPlayerSkins
    DROP CONSTRAINT FK_ChannelPlayerSkins_Channels

但显然，在我们使用的其他一些数据库上，约束具有不同的名称.如何检查是否存在名称为 FK_ChannelPlayerSkins_Channels 的约束.

but apparently, on some other databases we use, the constraint has a different name. How do I check if there's a constraint with the name FK_ChannelPlayerSkins_Channels.

推荐答案

试试这个:

SELECT
    * 
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
    WHERE CONSTRAINT_NAME ='FK_ChannelPlayerSkins_Channels'

-- 编辑 --

当我最初回答这个问题时，我在想外键"，因为最初的问题是关于查找FK_ChannelPlayerSkins_Channels".从那时起，许多人都在评论寻找其他约束"，这里有一些其他的问题:

When I originally answered this question, I was thinking "Foreign Key" because the original question asked about finding "FK_ChannelPlayerSkins_Channels". Since then many people have commented on finding other "constraints" here are some other queries for that:

--Returns one row for each CHECK, UNIQUE, PRIMARY KEY, and/or FOREIGN KEY
SELECT * 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_NAME='XYZ'  


--Returns one row for each FOREIGN KEY constrain
SELECT * 
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
    WHERE CONSTRAINT_NAME='XYZ'


--Returns one row for each CHECK constraint 
SELECT * 
    FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
    WHERE CONSTRAINT_NAME='XYZ'

这是另一种方法

--Returns 1 row for each CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY, and/or DEFAULT
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'
        AND OBJECT_NAME(OBJECT_ID)='XYZ'

如果您需要更多约束信息，请查看系统存储过程master.sys.sp_helpconstraint 以了解如何获取某些信息.要使用 SQL Server Management Studio 查看源代码，请进入对象资源管理器".从那里展开主"数据库，然后展开可编程性"，然后是存储过程"，然后是系统存储过程".然后您可以找到sys.sp_helpconstraint"并右键单击它并选择修改".请注意不要对其进行任何更改.此外，您可以像 EXEC sp_helpconstraint YourTableNameHere 一样在任何表上使用此系统存储过程.

If you need even more constraint information, look inside the system stored procedure master.sys.sp_helpconstraint to see how to get certain information. To view the source code using SQL Server Management Studio get into the "Object Explorer". From there you expand the "Master" database, then expand "Programmability", then "Stored Procedures", then "System Stored Procedures". You can then find "sys.sp_helpconstraint" and right click it and select "modify". Just be careful to not save any changes to it. Also, you can just use this system stored procedure on any table by using it like EXEC sp_helpconstraint YourTableNameHere.

这篇关于如何检查Sql server中是否存在约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！