How to check if DB Constraints are enabled in a database?

sarada7@gmail.com wrote:
> How to check if DB Constraints are enabled in a database?

What kind of constraints? Foreign key constraints? Check constraints?
They really can't be "enabled" or "disabled." Either they exist or
they don't.

Yes, you are right constraints exist or they don't. My question was is
there any way to verify if any check is existing on a table before
enabling one?

For example, we add a constraint using the following SQL

ALTER TABLE Customers CHECK CONSTRAINT ALL

and disable them using

ALTER TABLE Customers NOCHECK CONSTRAINT ALL

If we run the SQL to disable the constraint. At this point how to
verify if the constraint is not existing?

sarada7@gmail.com wrote:
> Yes, you are right constraints exist or they don't. My question was is
> there any way to verify if any check is existing on a table before
> enabling one?
>
> For example, we add a constraint using the following SQL
>
> ALTER TABLE Customers CHECK CONSTRAINT ALL
>
> and disable them using
>
> ALTER TABLE Customers NOCHECK CONSTRAINT ALL
>
> If we run the SQL to disable the constraint. At this point how to
> verify if the constraint is not existing?

Do something that violates the constraint and see if the server
complains.

Thanks ZeldorBlat for your reply,

Other than violating the constraint and catching it, is there any
supported command which check this directly.

Thanks in advance

sp_helpconstraint returns constraint information

(sarada7@gmail.com) writes:
> How to check if DB Constraints are enabled in a database?

SELECT name, tbl = object_name(parent_obj)
FROM sysobjects
WHERE objectproperty(id, 'CnstIsDisabled') = 1

SELECT name, tbl = object_name(parent_obj)
FROM sysobjects
WHERE objectproperty(id, 'CnstIsNotTrusted') = 1

The latter returns constraints that are enabled, but that were enabled
WITH NOCHECK, that is without checking whether the current data was
valid. The optimizer only ignores constraints that are not trusted, and
this can have serious performance impacts, particular with partitioned
views.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx