SQL Server的：如何使服务器检查其所有检查约束？(SQL Server: How to ma

看来，通过企业管理器*生成一些脚本（或没有，也没关系）WITH NOCHECK创建检查约束。

现在，当有人修改表， SQL服务器跨失败的检查约束磕磕绊绊，并引发错误。

我可以把SQL经历了所有检查约束，并检查他们？

运行：

sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

仅启用先前禁用检查约束，它实际上并没有检查。

脚注

* SQL Server 2000中

Answer 1:

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS实际上不会让你的约束信任。它会报告违反约束的任何行。真正让您的所有可信的约束，你可以做到以下几点：

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

在SQL Server 2000中，你可以找到任何不信任的约束有：

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

约束，然后再重新启用与检查 ：

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

注：在最后声明中， WITH CHECK CHECK是不是一个错字。 “有票”将检查所有表数据，以确保不会有违规行为，并会约束信任，而检查将确保约束已启用。

参见： 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:

找到它：

检查在当前数据库中所有表的所有约束，约束是否被启用与否：

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

要只检查启用的约束：

DBCC CHECKCONSTRAINTS

Answer 3:

做这个：

ALTER TABLE dbo.Test
      WITH CHECK CHECK CONSTRAINT CK_Test;

说明：你能信任你的约束？