DBCC CHECKCONSTRAINTS

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

Syntax

DBCC CHECKCONSTRAINTS
    [( 'table_name' | 'constraint_name'
    )]

    [ WITH { ALL_ERRORMSGS | ALL_CONSTRAINTS } ]

Arguments

'table_name' | 'constraint_name'

Is the table or constraint to be checked. If table_name is specified, all enabled constraints on that table are checked. If constraint_name is specified, only that constraint is checked. If neither a table_name nor a constraint_name 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 Using Identifiers.

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 checked. The default is the first 200 rows.

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 will be of the 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. When all requested tables or constraints have been checked, the result set is returned.

DBCC CHECKCONSTRAINTS checks the integrity of foreign key and checked constraints, but does not check the integrity of a table's on-disk data structures. These data structure checks can be performed with DBCC CHECKDB and DBCC CHECKTABLE.

Result Sets

DBCC CHECKCONSTRAINTS return a rowset with the following columns.

For example, a DBCC CHECKCONSTRAINT on the orders table yields the following result.

Table Name   Constraint Name      Where
-----------   -----------------------   -----------------------
orders      PartNo_FKey         PartNo = '12'

The value PartNo = '12' in the Where column can be used in a SELECT statement that identifies the row violating the constraint PartNo_FKEY.

Select * 
From orders 
Where PartNo = '12'

The user then may decide to modify, delete or otherwise adjust the rows.

Permissions

DBCC CHECKCONSTRAINTS permissions default to members of the sysadmin fixed server role and the db_owner fixed database role, and are not transferable.

Examples

A. Check a table.

This example checks the constraint integrity of the orders table in the pubs database.

DBCC CHECKCONSTRAINTS ('authors')
GO

B. Check a specific constraint

This example checks the integrity of the PartNo_FKey constraint. The constraint name uniquely identifies the table it is declared upon.

DBCC CHECKCONSTRAINTS ('PartNo_Fkey')
GO

C. Check all enabled and disabled constraints on all tables

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

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS
GO