DBCC CHECKCONSTRAINTS (Transact-SQL)

Checks the integrity of a specified constraint or all constraints on a specified table in the current database.

Transact-SQL Syntax Conventions

Syntax

DBCC CHECKCONSTRAINTS
[ 
        ( 
        table_name | table_id | constraint_name | constraint_id 
        )
]
    [ WITH 
        [ { ALL_CONSTRAINTS | ALL_ERRORMSGS } ]
    [ , ] [ NO_INFOMSGS ] 
    ]

Arguments

• table_name | table_id | constraint_name | constraint_id
  Is the table or constraint to be checked. If neither table_name nor table_id is specified, all enabled constraints on that table are checked. If constraint_name or constraint_id is specified, only that constraint is checked. If neither a table identifier nor a constraint identifier is specified, all enabled constraints on all tables in the current database are checked.

  A constraint name uniquely identifies the table to which it belongs. For more information, see Identifiers.

• WITH
  Enables options to be specified

• ALL_CONSTRAINTS
  Checks all enabled and disabled constraints on the table if the table name is specified or if all tables are checked; otherwise, checks only the enabled constraint. ALL_CONSTRAINTS has no effect when a constraint name is specified.

• ALL_ERRORMSGS
  Returns all rows that violate constraints in the table that is checked. The default is the first 200 rows.

• NO_INFOMSGS
  Suppresses all informational messages.

Result Sets

DBCC CHECKCONSTRAINTS return a rowset with the following columns.

Column name	Data type	Description
Table Name	varchar	Name of the table.
Constraint Name	varchar	Name of the constraint that is violated.
Where	varchar	Column value assignments that identify the row or rows violating the constraint. The value in this column can be used in a WHERE clause of a SELECT statement querying for rows that violate the constraint.

Remarks

DBCC CHECKCONSTRAINTS constructs and executes a query for all FOREIGN KEY constraints and CHECK constraints on a table.

For example, a foreign key query is of the following form:

SELECT <columns>
FROM <table_being_checked> LEFT JOIN <referenced_table>
    ON <table_being_checked.fkey1> = <referenced_table.pkey1> 
    AND <table_being_checked.fkey2> = <referenced_table.pkey2>
WHERE <table_being_checked.fkey1> IS NOT NULL 
    AND <referenced_table.pkey1> IS NULL
    AND <table_being_checked.fkey2> IS NOT NULL
    AND <referenced_table.pkey2> IS NULL

The query data is stored in a temp table. After all requested tables or constraints have been checked, the result set is returned.

DBCC CHECKCONSTRAINTS checks the integrity of FOREIGN KEY and CHECK constraints but does not check the integrity of the on-disk data structures of a table. These data structure checks can be performed by using DBCC CHECKDB and DBCC CHECKTABLE.

Permissions

Requires membership in the sysadmin fixed server role or the db_owner fixed database role.

Examples

A. Checking a table

The following example checks the constraint integrity of the table Table1 in the AdventureWorks database.

USE AdventureWorks;
GO
CREATE TABLE Table1 (Col1 int, Col2 char (30));
GO
INSERT INTO Table1 VALUES (100, 'Hello');
GO
ALTER TABLE Table1 WITH NOCHECK ADD CONSTRAINT chkTab1 CHECK (Col1 > 100);
GO
DBCC CHECKCONSTRAINTS(Table1);
GO

B. Checking a specific constraint

The following example checks the integrity of the CK_ProductCostHistory_EndDate constraint in the Production schema.

USE AdventureWorks;
GO
DBCC CHECKCONSTRAINTS ("Production.CK_ProductCostHistory_EndDate");
GO

C. Checking all enabled and disabled constraints on all tables

The following example checks the integrity of all enabled and disabled constraints on all tables in the current database.

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS;
GO

See Also

Reference

DBCC CHECKDB (Transact-SQL)
DBCC CHECKTABLE (Transact-SQL)
DBCC (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
5 December 2005	Changed content: Changed syntax diagram to indicate that NO_INFOMSGS can be specified in the WITH clause without { ALL_CONSTRAINTS | ALL_ERRORMSGS }.