Posted by Erland Sommarskog on 03/08/06 01:16

(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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]