DBCC CHECKTABLE (T-SQL)

Checks the integrity of the data, index, text, ntext, and image pages for the specified table.

Syntax

DBCC CHECKTABLE
    (    'table_name'
            [,     NOINDEX
                | index_id
                |    {    REPAIR_ALLOW_DATA_LOSS
                        | REPAIR_FAST
                        | REPAIR_REBUILD
                    }]
    ) [WITH {ALL_ERRORMSGS | NO_INFOMSGS}]

Arguments

'table_name'

Is the table for which to check data page integrity. Table names must conform to the rules for identifiers. For more information, see Using Identifiers.

NOINDEX

Specifies that nonclustered indexes for nonsystem tables should not be checked.

REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST| REPAIR_REBUILD

Specifies that DBCC CHECKTABLE repair the found errors. The database must be in single-user mode to use a repair option and can be one of the following.

 

Value	Description
REPAIR_ALLOW_DATA_LOSS	Performs all repairs done by REPAIR_REBUILD and includes allocation and deallocation of rows and pages for correcting allocation errors, structural row or page errors, and deletion of corrupted text objects. These repairs can result in some data loss. The repair may be done under a user transaction to allow the user to roll back the changes made. If repairs are rolled back, the database will still contain errors and should be restored from a backup. If a repair for an error has been skipped due to the provided repair level, any repairs that depend on the repair are also skipped. After repairs are completed, back up the database.
REPAIR_FAST	Performs minor, nontime-consuming repair actions such as repairing extra keys in nonclustered indexes. These repairs can be done quickly and without risk of data loss.
REPAIR_REBUILD	Performs all repairs done by REPAIR_FAST and includes time-consuming repairs such as rebuilding indexes. These repairs can be done without risk of data loss.

index_id

Is the index identification number (ID) for which to check data page integrity. If an index_id is specified, DBCC CHECKTABLE checks only that index.

WITH

Specifies an option for the number of error messages to be returned. If neither ALL_ERRORMSGS nor NO_INFOMSGS is specified, Microsoft® SQL Server™ returns all error messages.

ALL_ERRORMSGS

Displays all error messages. If not specified, SQL Server displays a maximum of 200 error messages per table. Error messages are sorted by object ID.

NO_INFOMSGS

Suppresses all informational messages and the report of space used.

Remarks

• For the specified table, DBCC CHECKTABLE checks that:
• Index and data pages are correctly linked.
• Indexes are in their proper sorted order.
• Pointers are consistent.
• The data on each page is reasonable.
• Page offsets are reasonable.

DBCC CHECKTABLE checks the linkages and sizes of text, ntext and image pages for the specified table. However, DBCC CHECKTABLE does not check the allocations of pages in the specified table. Use DBCC CHECKALLOC to check page allocations.

DBCC CHECKTABLE requires a shared lock on all tables and indexes in the database for the duration of the operation.

To perform DBCC CHECKTABLE on every table in the database, use DBCC CHECKDB.

Result Sets

DBCC CHECKTABLE returns this result set (same result set is returned if you specify only the table name or if you provide any of the options); this example specifies the authors table in the pubs database (values may vary):

DBCC results for 'authors'.

There are 23 rows in 1 pages for object 'authors'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Permissions

DBCC CHECKTABLE permissions default to members of the sysadmin fixed server role or the db_owner fixed database role, or the table owner, and are not transferable.

Examples

A. Check a specific table

This example checks the data page integrity of the authors table.

DBCC CHECKTABLE ('authors')

GO

B. Check the table without checking nonclustered indexes

This example checks the data page integrity of the authors table without checking nonclustered indexes.

DBCC CHECKTABLE ('authors', NOINDEX)

GO

C. Check a specific index

This example checks a specific index, which is obtained by accessing sysindexes.

USE pubs

DECLARE @indid int

SELECT @indid = indid

FROM sysindexes

WHERE id = OBJECT_ID('authors') AND name = 'aunmind'

DBCC CHECKTABLE ('authors', @indid)

GO

See Also