DBCC CHECKCONSTRAINTS

Question

Post reply

DBCC CHECKCONSTRAINTS

Jeff-67583

SSCertifiable

Points: 5625
More actions
November 11, 2004 at 10:38 am

#85984

Hello,
What does DBCC CHECKCONSTRAINTS actually do, and is there another similar method for this that is not restricted to the db_owner or DBA?
Many thanks. Jeff

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Guus Kramer Ten Centuries Points: 1031 More actions · Answer 1

See BOL (2004):

What do you want to do exactely???

Gkramer

The Netherlands

***************************************

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 columnsFROM table_being_checked LEFT JOIN referenced_table   ON table_being_checked.fkey1 = referenced_table.pkey1    AND table_being_checked.fkey2 = referenced_table.pkey2WHERE 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.

Column name

Data type

Description

Table Name

varchar

Name of the table.

Constraint Name

varchar

Name of the constraint violated.

Where

varchar

Column value assignments that identify the row or rows violating the constraint.

The value in this column may be used in a WHERE clause of a SELECT statement querying for rows violating the constraint.

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

***************************************

Jeff-67583 SSCertifiable Points: 5625 More actions · Answer 2

Hi,

I have a developer that would like to determine what data in a column/table violates the constraint once the constraint has been re-enabled.

Many thanks. Jeff

Guus Kramer Ten Centuries Points: 1031 More actions · Answer 3

There are a few undocumented stored procedures on this but I have to look it up,

Ill keep in touch.

GKramer

The Netherlands