SQL Server: How to make server check all its check constraints?

Question

It seems that some scripts generated by Enterprise Manager* (or not, it doesn't matter) created check constraints WITH NOCHECK.

Now when anyone modifies the table, SQL Server is stumbling across failed check constraints, and throwing errors.

Can i make SQL go through all its check constraints, and check them?

Running:

sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

only enables previously disabled check constraints, it doesn't actually check them.

Footnotes

* SQL Server 2000

Answer 1

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS won't actually make your constraints trusted. It will report any rows that violate the constraints. To actually make all of your constraints trusted, you can do the following:

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS --This reports any data that violates constraints.

--This reports all constraints that are not trusted
SELECT OBJECT_NAME(parent_object_id) AS table_name, name, is_disabled  
FROM sys.check_constraints 
WHERE is_not_trusted = 1
UNION ALL
SELECT OBJECT_NAME(parent_object_id) AS table_name, name, is_disabled  
FROM sys.foreign_keys
WHERE is_not_trusted = 1
ORDER BY table_name

In SQL Server 2000 you can find any untrusted constraints with:

--Reports all constraints that are not trusted (SQL 2000)
SELECT name, type, status,
    (status & 2048) AS IsTrusted,
    (status & 256) AS IsEnabled,
    OBJECTPROPERTY(id,'CnstIsNotTrusted') as is_not_trusted,
    OBJECTPROPERTY(id,'CnstIsDisabled') as is_disabled
FROM sysobjects 
WHERE type IN ('C', 'F') --C=Constraint, F=Foreign Key
AND OBJECTPROPERTY(id,'CnstIsNotTrusted') <> 0
AND OBJECTPROPERTY(id,'CnstIsDisabled') = 0

Constraints are then re-reenabled with check:

--This makes all constraints trusted
-- but first anything reported by DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS must be fixed.
exec sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'

Note: on the last statement, the WITH CHECK CHECK is not a typo. The "WITH CHECK" will check all table data to ensure there are not violations, and will make the constraint trusted, while the check will make sure the constraints is enabled.

See also: http://sqlblog.com/blogs/tibor_karaszi/archive/2008/01/12/non-trusted-constraints.aspx

http://sqlblog.com/blogs/tibor_karaszi/archive/2008/01/12/non-trusted-constraints-and-performance.aspx