dbcc checktable

dbcc checktable checks the specified table to see that:

Index and data pages are linked correctly
Indexes are sorted properly
Pointers are consistent
Data rows on each page have entries in the row-offset table; these entries match the locations for the data rows on the page
Data rows on each page have entries in the row-offset table in the page that match their respective locations on the page
Partition statistics for partitioned tables are correct

The syntax for dbcc checktable is:

dbcc checktable ({table_name | table_id}
     [, skip_ncindex] )

The skip_ncindex option allows you to skip checking the page linkage, pointers, and sort order on nonclustered indexes. The linkage and pointers of clustered indexes and data pages are essential to the integrity of your tables. You can drop and re-create nonclustered indexes if Adaptive Server reports problems with page linkage or pointers.

When checkstorage returns a fault code of 100035, and checkverify confirms that the spacebit fault is a hard fault, you can use dbcc checktable to fix the reported fault.

The following is the syntax, where table_name is the name of the table to repair:

dbcc checktable (table_name, fix_spacebits)

dbcc checktable can be used with the table name or the table’s object ID. The sysobjects table stores this information in the name and id columns.

The following example shows a report on an undamaged table:

dbcc checktable(titles)
go

Checking titles
The total number of data pages in this table is 3.
Table has 18 data rows.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

If the table is partitioned, dbcc checktable checks data page linkage and partition statistics for each partition. For example:

dbcc checktable(historytab)
go

Checking historytab
The total number of pages in partition 1 is 20.
The total number of pages in partition 2 is 17.
The total number of pages in partition 3 is 19.
The total number of pages in partition 4 is 17.
The total number of pages in partition 5 is 20.
The total number of pages in partition 6 is 16.
The total number of pages in partition 7 is 19.
The total number of pages in partition 8 is 17.
The total number of pages in partition 9 is 19.
The total number of pages in partition 10 is 16.

The total number of data pages in this table is 190.
Table has 1536 data rows.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

To check a table that is not in the current database, supply the database name. To check a table owned by another object, supply the owner’s name. You must enclose any qualified table name in quotes. For example:

dbcc checktable("pubs2.newuser.testtable")

dbcc checktable addresses the following problems:

If the page linkage is incorrect, dbcc checktable displays an error message.
If the sort order (sysindexes.soid) or character set (sysindexes.csid) for a table with columns with char or varchar datatypes is incorrect, and the table’s sort order is compatible with Adaptive Server’s default sort order, dbcc checktable corrects the values for the table. Only the binary sort order is compatible across character sets.

If you change sort orders, character-based user indexes are marked “read-only” and must be checked and rebuilt, if necessary. See Chapter 7, “Configuring Character Sets, Sort Orders, and Languages,” for more information about changing sort orders.
If data rows are not accounted for in the first OAM page for the object, dbcc checktable updates the number of rows on that page. This is not a serious problem. The built-in function rowcnt uses this value to provide fast row estimates in procedures like sp_spaceused.

You can improve dbcc checktable performance by using enhanced page fetching.