DBCC CHECKTABLE

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

Syntax

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

Arguments

'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.

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.

index_id

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.

WITH

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.

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.

TABLOCK

Causes DBCC CHECKTABLE to obtain a shared table lock.

ESTIMATE ONLY

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

PHYSICAL_ONLY

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.

Remarks

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) 
-------------------------------------------------- 
2

(1 row(s) affected)

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') WITH PHYSICAL_ONLY
GO

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)
GO

See Also

DBCC

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