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

do this:

ALTER TABLE dbo.Test
      WITH CHECK CHECK CONSTRAINT CK_Test;

Explanation: Can you trust your constraints?

Answer 2

Found it:

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

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

To check only enabled constraints:

DBCC CHECKCONSTRAITNS

Answer 3

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 
FROM sys.check_constraints 
WHERE is_not_trusted = 1
UNION ALL
SELECT OBJECT_NAME(parent_object_id) AS table_name, name 
FROM sys.foreign_keys
WHERE is_not_trusted = 1
ORDER BY table_name


--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 that 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