If you do not provide a database name, dbcc checkalloc checks the current database.
All pages are correctly allocated
Partition statistics on the allocation pages are correct
Every page that is allocated is used
All pages are correctly allocated to individual partitions
Only allocated pages are used
With the fix option, dbcc checkalloc fixes all allocation errors that would otherwise be fixed by dbcc tablealloc, and also fixes pages that remain allocated to objects that have been dropped from the database. Before you can use dbcc checkalloc with the fix option, you must put the database into single-user mode.
Tables without clustered indexes have an INDID=0.
For allpages locking tables with clustered indexes, the table data partitions and clustered index partitions are consolidated, with an INDID=1 for the data partitions (or the clustered index partitions).
For dataonly locked tables with clustered index, the table data partitions have an INDID=0. The clustered index and nonclustered indexes are numbered consecutively, starting with INDID=2.
The following report on pubs2 shows output for the titleauthor, titles, and stores tables:
*************************************************************** TABLE: titleauthor OBJID = 544001938 PARTITION ID=544001938 FIRST=904 ROOT=920 SORT=1 Data level: indid 1, partition 544001938. 1 Data pages allocated and 2 Extents allocated. Indid : 1, partition : 544001938. 1 Index pages allocated and 2 Extents allocated. PARTITION ID=544001938 FIRST=928 ROOT=928 SORT=0 Indid : 2, partition : 544001938. 1 Index pages allocated and 2 Extents allocated. PARTITION ID=544001938 FIRST=944 ROOT=944 SORT=0 Indid : 3, partition : 544001938. 1 Index pages allocated and 2 Extents allocated. TOTAL # of extents = 8 *************************************************************** TABLE: titles OBJID = 576002052 PARTITION ID=1120003990 FIRST=1282 ROOT=1282 SORT=1 Data level: indid 0, partition 1120003990. 1 Data pages allocated and 1 Extents allocated. PARTITION ID=1136004047 FIRST=1289 ROOT=1289 SORT=1 Data level: indid 0, partition 1136004047. 1 Data pages allocated and 1 Extents allocated. TOTAL # of extents = 2 *************************************************************** TABLE: stores OBJID = 608002166 PARTITION ID=608002166 FIRST=745 ROOT=745 SORT=0 Data level: indid 0, partition 608002166. 1 Data pages allocated and 1 Extents allocated. TOTAL # of extents = 1