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

Answer 2

Found it:

Checks all constraints on all tables in the current database, whether the constraint is enabled or not:

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS 

To check only enabled constraints:

DBCC CHECKCONSTRAINTS