Was this page helpful?
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All


SQL Server 2000

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


    ( 'table_name' | 'view_name'
            | index_id
            | { REPAIR_ALLOW_DATA_LOSS
                | REPAIR_FAST
                | REPAIR_REBUILD }
                    [ , [ TABLOCK ] ]
                    [ , [ ESTIMATEONLY ] ]
                    [ , [ PHYSICAL_ONLY ] ]


'table_name' | 'view_name'

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


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


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.


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


Specifies options on the number of error messages returned, locks obtained, or estimating tempdb requirements. If neither ALL_ERRORMSGS nor NO_INFOMSGS is specified, Microsoft® SQL Server™ returns all error messages.


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.


Suppresses all informational messages and the report of space used.


Causes DBCC CHECKTABLE to obtain a shared table lock.


Displays the estimated amount of tempdb space needed to run DBCC CHECKTABLE with all of the other specified options.


Limits the checking to the integrity of the physical structure of the page and record headers, and to the consistency between the pages' object ID and index ID and the allocation structures. Designed to provide a low overhead check of the physical consistency of the database, this check also detects torn pages and common hardware failures that can compromise a user's data. PHYSICAL_ONLY always implies NO_INFOMSGS and is not allowed with any of the repair options.


DBCC CHECKTABLE performs a physical consistency check on tables and indexed views. The NOINDEX option, used only for backward compatibility, also applies to indexed views.

For the specified table, DBCC CHECKTABLE checks that:

  • Index and data pages are correctly linked.

  • Indexes are in their proper sort 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 verify the consistency of all the allocation structures in the database. Use DBCC CHECKALLOC to do this verification.

DBCC CHECKTABLE does not acquire a table lock by default. Instead, it acquires a schema lock that prevents meta data changes but allows changes to the data. The DBCC statement collects information, then scans the log for any additional changes made, merging the two sets of information together to produce a consistent view of the data at the end of the scan.

When the TABLOCK option is specified, DBCC CHECKTABLE acquires a shared table lock. This allows more detailed error messages for some classes of errors and minimizes the amount of tempdb space required by avoiding the use of transaction log data.

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

By default, DBCC CHECKTABLE performs parallel checking of objects. The degree of parallelism is determined automatically by the query processor. The maximum degree of parallelism is configured in the same manner as that of parallel queries. Use the sp_configure system stored procedure to restrict the maximum number of processors available for DBCC checking. For more information, see max degree of parallelism Option.

Parallel checking can be disabled by using trace flag 2528. For more information, see Trace Flags.

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.

DBCC CHECKTABLE returns this result set when the ESTIMATEONLY option is specified.

Estimated TEMPDB space needed for CHECKTABLES (KB) 

(1 row(s) affected)

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

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.

A. Check a specific table

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

B. Check the table without checking nonclustered indexes

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

C. Check a specific index

This example checks a specific index, 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)

See Also


Features Supported by the Editions of SQL Server 2000

How to configure the number of processors available for parallel queries (Enterprise Manager)

Table and Index Architecture

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
© 2015 Microsoft