dbcc checktable

dbcc checktable performs a series of checks on the specified table.

dbcc checktable verifies that:

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

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 SAP ASE reports problems with page linkage or pointers.

partition_name is the name of the partition you are checking (this may or may not contain the entire table because tables can span multiple partitions), and partition_id is the ID of the partition you are checking.

If you specify partition_name or partition_id, dbcc checktable checks only the table, or parts of the table, residing on this partition; it does not expand its check to other partitions, and has the following restrictions:

If the table consists of more than one partition, index processing is limited to local indexes.
If you specify the partition_name or partition_id parameter, you must also specify either the second parameter (skip_ncindex or fix_spacebits) or null. This example specifies null:
```
dbcc checkalloc(titles, null, 560001995)
```
If the sort order or character set for a table with columns defined with char or varchar datatypes is incorrect, dbcc checktable does not corrct these values. You must run dbcc checktable on the entire table to correct these errors.
If an index is marked “read-only” due to a change in the sort order, dbcc checktable does not clear the O_READONLY bit in the sysstat field for the table’s sysobjects entry. To clear this status bit, run dbcc checktable on the entire table.
If you run dbcc checktable on syslogs, dbcc checktable does not report space usage (free space versus used space). However, if you do not specify partition_name or partition_id parameters, dbcc checktable reports the space usage.

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 command checks part of the titles table that resides on the smallsales partition (which contains all book sales less than 5000):

dbcc checktable(titles, NULL, "smallsales")

Checking partition 'smallsales' (partition ID 1120003990) of table 'titles'.
The logical page size of this table is 8192 bytes. The total number of data
pages in partition 'smallsales' (partition ID 1120003990) is 1.
Partition 'smallsales' (partition ID 1120003990) has 14 data rows.
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.

You can use dbcc checktable 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)

Checking table 'titles' (object ID 576002052):Logical page size is 8192 bytes.
The total number of data pages in partition 'titleidind_576002052' (partition ID
576002052) is 1.
The total number of data pages in this table is 1.
Table has 18 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 SAP ASE default sort order, dbcc checktable corrects the values for the table. Only the binary sort order is compatible across character sets.

Note: If you change sort orders, character-based user indexes are marked read-only and must be checked and rebuilt, if necessary.
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 row_count uses this value to provide fast row estimates in procedures like sp_spaceused.

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