sql-serverinformation-schemasystem-tables

How to check if a Constraint exists in Sql server?


I have this sql:

ALTER TABLE dbo.ChannelPlayerSkins
    DROP CONSTRAINT 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.


Solution

  • try this:

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

    -- EDIT --

    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'
    

    here is an alternate method

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