How to check if a Constraint exists in Sql server

Question

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

User · Answer

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

User · Answer

Just something to watch out for        In SQL Server 2008 R2 SSMS  the  Script Constraint as -  DROP And CREATE To  command produces T-SQL like below  USE  MyDatabase  GO  IF  EXISTS  SELECT   FROM dbo sysobjects WHERE id   OBJECT ID N  DEF Detail IsDeleted    AND type    D   BEGIN ALTER TABLE  Patient   Detail  DROP CONSTRAINT  DEF Detail IsDeleted  END  GO  USE  MyDatabase  GO  ALTER TABLE  Patient   Detail  ADD  CONSTRAINT  DEF Detail IsDeleted   DEFAULT   0   FOR  IsDeleted  GO   Out of the box  this script does NOT drop the constraint because the SELECT returns 0 rows    see post Microsoft Connect     The name of the default constraint is wrong but I gather it also has something to do with the OBJECT ID function because changing the name doesn t fix the problem   To fix this  I removed the usage of OBJECT ID and used the default constraint name instead    SELECT   FROM dbo sysobjects WHERE  name     N DEF Detail IsDeleted   AND type    D

User · Answer

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

User · Answer

IF  OBJECT ID  FK ChannelPlayerSkins Channels   IS NOT NULL

User · Answer

I use the following query to check for an existing constraint before I create it   IF  NOT EXISTS SELECT 1 FROM sysconstraints WHERE OBJECT NAME constid     UX CONSTRAINT NAME  AND OBJECT NAME id     TABLE NAME    BEGIN     END   This queries for the constraint by name targeting a given table name  Hope this helps

User · Answer

Easiest way to check for the existence of a constraint  and then do something such as drop it if it exists  is to use the OBJECT ID   function     IF OBJECT ID  dbo  CK ConstraintName     C   IS NOT NULL      ALTER TABLE dbo  tablename  DROP CONSTRAINT CK ConstraintName   OBJECT ID can be used without the second parameter   C  for check constraints only  and that may also work  but if your constraint name matches the name of other objects in the database you may get unexpected results     IF OBJECT ID  dbo  CK ConstraintName    IS NOT NULL      ALTER TABLE dbo  tablename  DROP CONSTRAINT CK ConstraintName   OBJECT ID can also be used with other  constraints  such as Foreign Key constraints or Primary Key constraints  etc   For best results  always include the appropriate object type as the second parameter for the OBJECT ID function   Constraint Object Types    C   CHECK constraint D   DEFAULT  constraint or stand-alone  F   FOREIGN KEY constraint PK   PRIMARY KEY constraint R   Rule  old-style  stand-alone  UQ   UNIQUE constraint   Also note that the schema is often required   The schema of constraints generally takes the schema of the parent table     Failure to put your constraints  or whatever you are checking  in brackets when using this method may also cause a false negative -- if your object uses unusual characters  such as a     the brackets are required

User · Answer

Are you looking at something like this  below is tested in SQL Server 2005  SELECT   FROM sys check constraints WHERE  object id   OBJECT ID N  dbo   CK accounts    AND  parent object id   OBJECT ID N  dbo    accounts

User · Answer

If you are looking for other type of constraint  e g  defaults  you should use different query  From How do I find a default constraint using INFORMATION SCHEMA  answered by devio   Use   SELECT   FROM sys objects WHERE type    D  AND name    name   to find a default constraint by name   I ve put together different  IF not Exists  checks in my post  DDL  IF not Exists  conditions to make SQL scripts re-runnable

User · Answer

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

User · Answer

I use this to check for and remote constraints on a column  It should have everything you need   DECLARE    ps TableName VARCHAR 300       ps ColumnName VARCHAR 300   SET  ps TableName    mytable  SET  ps ColumnName    mycolumn   DECLARE c ConsList CURSOR LOCAL STATIC FORWARD ONLY FOR     SELECT      ALTER TABLE     RTRIM tb name      drop constraint     sco name AS csql     FROM         sys Objects tb         INNER JOIN sys Columns tc on  tb Object id   tc object id          INNER JOIN sys sysconstraints sc ON  tc Object ID   sc id and tc column id   sc colid          INNER JOIN sys objects sco ON  sc Constid   sco object id      where         tb name  ps TableName         AND tc name  ps ColumnName OPEN c ConsList FETCH c ConsList INTO  ls SQL WHILE    FETCH STATUS   0  BEGIN      IF RTRIM ISNULL  ls SQL        lt  gt     BEGIN         EXECUTE  ls SQL      END     FETCH c ConsList INTO  ls SQL END CLOSE c ConsList DEALLOCATE c ConsList

User · Answer

INFORMATION SCHEMA is your friend  It has all kinds of views that show all kinds of schema information  Check your system views  You will find you have three views dealing with constraints  one being CHECK CONSTRAINTS

User · Answer

You can use the one above with one caveat   IF EXISTS      SELECT 1 FROM sys foreign keys      WHERE parent object id   OBJECT ID N dbo TableName            AND name    CONSTRAINTNAME    BEGIN      ALTER TABLE TableName DROP CONSTRAINT CONSTRAINTNAME  END    Need to use the name    Constraint name  since a table may have multiple foreign keys and still not have the foreign key being checked for

User · Answer

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

[sql-server] How to check if a Constraint exists in Sql server?

Examples related to sql-server

Examples related to information-schema

Examples related to system-tables