IF EXISTS(SELECT TOP 1 1 FROM sys.default_constraints WHERE parent_object_id = OBJECT_ID(N'[dbo].[ChannelPlayerSkins]') AND name = 'FK_ChannelPlayerSkins_Channels')
BEGIN
    DROP CONSTRAINT FK_ChannelPlayerSkins_Channels
END
GO

SELECT tabla.name as Tabla,

        restriccion.name as Restriccion, 
        restriccion.type as Tipo, 
        restriccion.type_desc as Tipo_Desc
FROM {DATABASE_NAME}.sys.objects tabla 

INNER JOIN {DATABASE_NAME}.sys.objects restriccion

ON tabla.object_id = restriccion.parent_object_id

WHERE tabla.type = 'U' - Solo tablas creadas por el usuario.

AND restriccion.type = 'UQ' --Tipo de Restriccion UNIQUE

ORDER BY tabla.name, restriccion.type_desc                

如果你正在寻找其他类型的约束，例如默认值，你应该使用不同的查询 如何使用INFORMATION_SCHEMA找到默认约束?由devio回答)。使用:

SELECT * FROM sys.objects WHERE type = 'D' AND name = @name

按名称查找默认约束。

我已经把不同的“IF不存在”检查在我的帖子“DDL”IF不存在“条件使SQL脚本可重新运行”

IF EXISTS(SELECT TOP 1 1 FROM sys.default_constraints WHERE parent_object_id = OBJECT_ID(N'[dbo].[ChannelPlayerSkins]') AND name = 'FK_ChannelPlayerSkins_Channels')
BEGIN
    DROP CONSTRAINT FK_ChannelPlayerSkins_Channels
END
GO

IF (OBJECT_ID('FK_ChannelPlayerSkins_Channels') IS NOT NULL)

试试这个:

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

——编辑——

当我最初回答这个问题时，我在想“外键”，因为最初的问题要求找到“FK_ChannelPlayerSkins_Channels”。从那时起，许多人就寻找其他“约束”发表了评论，这里有一些其他的查询:

--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'

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.