By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,686 Members | 1,510 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,686 IT Pros & Developers. It's quick & easy.

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

P: n/a
How to check if DB Constraints are enabled in a database?

Mar 6 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a

sa*****@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.

Mar 6 '06 #2

P: n/a
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?

Mar 6 '06 #3

P: n/a

sa*****@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.

Mar 6 '06 #4

P: n/a
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

Mar 6 '06 #5

P: n/a
sp_helpconstraint returns constraint information

Mar 6 '06 #6

P: n/a
(sa*****@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, es****@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
Mar 7 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.