How to check if a SQL Server restriction exists?

Question

I have the following:

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

I want to check if the restriction exists before I omit it. I use the code above with type "U" for tables.

How do I change the code above (change xxxx) to check for it?

Answer 1

  SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 

or try this

  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' 

or

 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 

Answer 2

Try something like this

 IF OBJECTPROPERTY(OBJECT_ID('constraint_name'), 'IsConstraint') = 1 ALTER TABLE table_name DROP CONSTRAINT constraint_name 

Answer 3

you can do something like:

 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CONSTRAINT_NAME]') AND type in (N'U')) BEGIN .... END ELSE