Monitor Table Consistency with DBCC CHECKTABLE

Can you monitor the consistency of individual tables in SQL Server using the DBCC CHECKTABLE command? Yes. DBCC CHECKTABLE checks a single table, or indexed view, without running a check against the entire database. The previous lesson covered CHECKDB, CHECKALLOC, and CHECKCATALOG; CHECKTABLE rounds out that set as the fourth and narrowest of the four, since CHECKDB already runs CHECKTABLE against every table in the database as part of its own pass, the same way it already runs CHECKALLOC and CHECKCATALOG. CHECKTABLE exists for the case where you want to check one specific table on its own, after suspected corruption in that table specifically, or as a lighter weight spot check between full CHECKDB runs, not as a replacement for them.

DBCC CHECKTABLE
(
    table_name | view_name
    [ , { NOINDEX | index_id }
     | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD }
    ]
)
    [ WITH
        { [ ALL_ERRORMSGS ]
          [ , EXTENDED_LOGICAL_CHECKS ]
          [ , NO_INFOMSGS ]
          [ , TABLOCK ]
          [ , ESTIMATEONLY ]
          [ , { PHYSICAL_ONLY | DATA_PURITY } ]
          [ , MAXDOP = number_of_processors ]
        }
    ]

• table_name | view_name: the table or indexed view to check.
• NOINDEX | index_id: NOINDEX skips nonclustered indexes on the table, the same tradeoff CHECKDB offers. A specific index_id instead checks only that one index, plus the heap or clustered index underneath it, narrower than either a full check or NOINDEX.
• REPAIR_ALLOW_DATA_LOSS, REPAIR_FAST, REPAIR_REBUILD: repair options shaped the same as CHECKDB's, but worth double-checking before assuming they behave identically to CHECKALLOC's from the previous lesson, because they do not. REPAIR_FAST is still a no-op here, retained only for backward compatibility. REPAIR_REBUILD, which has no effect at all under CHECKALLOC, does genuine repair work for CHECKTABLE: it can restore missing nonclustered index rows or rebuild an index, with no risk of data loss. REPAIR_ALLOW_DATA_LOSS remains the last resort, the one option that can fix every error type CHECKTABLE finds, at the cost of possibly discarding corrupted data to do it.
• ALL_ERRORMSGS, NO_INFOMSGS, TABLOCK, ESTIMATEONLY, MAXDOP: behave the same as their CHECKDB counterparts from the previous lesson.
• EXTENDED_LOGICAL_CHECKS: adds logical checks on indexed views, XML indexes, and spatial indexes, at database compatibility level 100 or higher.
• PHYSICAL_ONLY: the lightweight option, suitable for frequent production use. It implies NO_INFOMSGS, cannot be combined with a repair option, and skips column integrity and FILESTREAM checks to stay fast.
• DATA_PURITY: checks for invalid or out of range column values, an out of range datetime value or invalid decimal precision, for example. These checks run automatically once a database has had a clean DBCC CHECKDB WITH DATA_PURITY run, so this option mainly matters for a database upgraded from an older SQL Server version that has not had that clean run yet.

A basic check against this course's Customers table:

DBCC CHECKTABLE ('dbo.Customers') WITH NO_INFOMSGS;

DBCC results for 'Customers'.
There are 9540 rows in 118 pages for object 'Customers'.

Two practical variants worth having on hand: a full check before trusting a table after suspected corruption, and a lighter one for routine use.

-- Full logical and physical check, used after suspected corruption
DBCC CHECKTABLE ('dbo.Customers') WITH ALL_ERRORMSGS, TABLOCK;

-- Lightweight physical check, suitable for frequent routine use
DBCC CHECKTABLE ('dbo.Orders') WITH PHYSICAL_ONLY;

For the table or view given, CHECKTABLE verifies all of the following:

• Index, in row, LOB, and row overflow pages are correctly linked to one another.
• Indexes are stored in the correct sort order.
• Pointers throughout the table's pages are consistent.
• The data on each page is reasonable, including the values in any computed columns.
• Page offsets are reasonable.
• Every row in the base table has a matching row in each nonclustered index, and every row in each nonclustered index has a matching row in the base table.
• For a partitioned table or index, every row sits in the partition it actually belongs to.
• Any FILESTREAM varbinary(max) data stays consistent with what the file system actually holds.

That list is worth having in full rather than a vaguer summary, since it is exactly how a table could pass a casual glance and still be wrong in a way only CHECKTABLE would catch: a row sitting in the wrong partition, say, or a nonclustered index row with no corresponding base table row left to match it.

Two things are worth keeping in mind before relying on CHECKTABLE as part of a regular routine, beyond the specific commands involved.

The first is resource cost. CHECKTABLE can consume meaningful CPU and I/O on a large table, enough to affect the rest of the instance if it runs during peak hours, which is exactly why the PHYSICAL_ONLY variant above exists: a lighter check for routine use, with a full check reserved for off peak hours or genuine suspicion of a problem.

The second is that CHECKTABLE only ever detects a problem; it does not fix one on its own unless a repair option is explicitly requested, and even then, a repair is not the first thing to reach for. If CHECKTABLE reports an error, the preferred next step is restoring the affected table, or the database, from a verified backup, since that recovers exactly what was there before without any risk of further data loss. A DBCC repair option, run through CHECKDB with REPAIR_ALLOW_DATA_LOSS if the error type requires it, is the option of last resort, used only when no usable backup exists, precisely because of what its own name says about what it might cost.

CHECKTABLE itself requires the same permission level as the other DBCC checks this course has covered, membership in the sysadmin fixed server role or the db_owner fixed database role, and it runs the same way against Azure SQL Database and Azure SQL Managed Instance as it does against an on premises or VM hosted instance, so a maintenance routine built around it does not need separate versions for each hosting model.

A different kind of problem from structural corruption is fragmentation, a condition where a table's or index's pages are not stored in a continuous, well ordered sequence on disk. Fragmentation does not mean the data is wrong the way a CHECKTABLE error does; it means SQL Server has to do more work reading it, particularly during a large range scan.

DBCC SHOWCONTIG is the older command for reporting fragmentation, and it is worth recognizing if you encounter it in an existing script, but it has been deprecated since SQL Server 2005, not something more recent, in favor of the sys.dm_db_index_physical_stats dynamic management view, which has been the documented, current replacement for exactly as long. Its legacy syntax:

DBCC SHOWCONTIG
[
( table_id [, index_id]
)
]

SHOWCONTIG takes a table's internal object_id rather than its name, found with SELECT OBJECT_ID('table_name') if you ever need to read an old script that still calls it. SHOWCONTIG falls into the same category as DBCC DBREINDEX from the previous lesson's roadmap: still technically present in SQL Server 2025, but not the command to actually write into a new script.

sys.dm_db_index_physical_stats returns fragmentation, page count, average page density, forwarded record count, and index depth, scoped to a table, an index, or an entire database:

sys.dm_db_index_physical_stats (
    { database_id | NULL },
    { object_id | NULL },
    { index_id | NULL },
    { partition_number | NULL },
    { mode }
)

Parameter	Description
database_id	ID of the database to check; DB_ID() returns the current database's ID. NULL scans every database on the instance.
object_id	ID of the table or view; NULL returns every object in the database.
index_id	ID of a specific index, 0 for a heap; NULL returns every index on the object.
partition_number	A specific partition; NULL returns every partition.
mode	LIMITED, SAMPLED, or DETAILED, covered below.

mode controls how much scanning the call actually does: LIMITED is the default and the fastest, reading only the smallest level of the index; SAMPLED reads roughly a 1 percent sample, falling back automatically to a full DETAILED scan on any index or heap under 10,000 pages, since a 1 percent sample of something that small would not be meaningful; DETAILED scans every page, the slowest option and the one that gives the most accurate numbers.

A query scoped to this course's own SalesDB, named columns rather than raw IDs:

SELECT
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID('SalesDB'), NULL, NULL, NULL, 'LIMITED') AS ips
JOIN sys.indexes AS i
    ON ips.object_id = i.object_id AND ips.index_id = i.index_id
ORDER BY ips.avg_fragmentation_in_percent DESC;

Permissions. Querying this view for objects in the current database needs CONTROL permission on the specific object, or VIEW DATABASE STATE, VIEW DATABASE PERFORMANCE STATE as of SQL Server 2022, a more granular alternative, to see every object in that database at once. VIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE, is only needed for the broader case of passing database_id = NULL to scan across every database on the instance in a single call; a query scoped to one database, the way the query above is written, does not need server level permission at all.

Deciding between reorganize and rebuild. Microsoft's current guidance here is more conditional than a flat percentage cutoff suggests. Fragmentation alone is not automatically a reason to act: its real cost is reduced read ahead efficiency during a large range scan, so an index sitting behind a workload that never does large scans gets little practical benefit from being defragmented no matter what its reported percentage says. Where action is warranted, Microsoft's own current example script still uses 30 percent as the switch point between the two operations, reorganize below it, rebuild at or above it, consistent with the commonly cited older guidance of reorganizing in the 5 to 30 percent range and rebuilding above 30 percent. A very small index, one spanning only a handful of pages, rarely benefits from either operation regardless of its percentage, which is why a maintenance script typically filters those out before acting on anything the DMV reports.

A script that automatically reorganizes or rebuilds fragmented indexes is a natural next step once you can query sys.dm_db_index_physical_stats, but it is worth building it directly against the DMV's own output rather than reaching for sp_msforeachtable, an undocumented and unsupported procedure not intended for this kind of targeted, per index work, and one that invites building a filter out of string matched object names, a fragile and unnecessarily risky pattern once dynamic SQL is already involved. A direct cursor over the DMV's results, branching between REORGANIZE and REBUILD the same way Microsoft's own example does, accomplishes the same goal more simply and more safely:

DECLARE @schemaName sysname, @tableName sysname, @indexName sysname, @frag float, @sql nvarchar(max);

DECLARE frag_cursor CURSOR FOR
SELECT s.name, t.name, i.name, ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
JOIN sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
JOIN sys.tables AS t ON ips.object_id = t.object_id
JOIN sys.schemas AS s ON t.schema_id = s.schema_id
WHERE ips.avg_fragmentation_in_percent > 5.0
  AND ips.page_count > 1000
  AND i.name IS NOT NULL;

OPEN frag_cursor;
FETCH NEXT FROM frag_cursor INTO @schemaName, @tableName, @indexName, @frag;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'ALTER INDEX ' + QUOTENAME(@indexName) + N' ON '
        + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName)
        + CASE WHEN @frag >= 30.0 THEN N' REBUILD;' ELSE N' REORGANIZE;' END;
    EXEC sp_executesql @sql;

    FETCH NEXT FROM frag_cursor INTO @schemaName, @tableName, @indexName, @frag;
END

CLOSE frag_cursor;
DEALLOCATE frag_cursor;

A few details worth noticing. The WHERE clause filters on both fragmentation percentage and page_count, so a tiny index that happens to be heavily fragmented, in percentage terms, does not trigger a pointless rebuild. The i.name IS NOT NULL condition excludes heaps, which have no index name for ALTER INDEX to target. QUOTENAME wraps every identifier before it goes into the dynamically built statement, which is not a stylistic flourish; it is what keeps an unusually named schema, table, or index from breaking, or worse, altering the meaning of, the generated SQL. Scheduled as a SQL Server Agent job, the same job scheduling tool this course covered earlier, this runs as a routine maintenance task rather than something triggered by hand after a problem is already noticed.

	DBCC SHOWCONTIG	sys.dm_db_index_physical_stats
Status	Deprecated since SQL Server 2005	Current, documented replacement
Identifies objects by	object_id only	object_id, joinable to sys.indexes for names
Scope	One table or index at a time	One index up to an entire instance at once
Scan depth control	None	LIMITED, SAMPLED, or DETAILED
Usable in a query join	No, console output only	Yes, a table valued function

The last row is the practical difference that matters most day to day: because sys.dm_db_index_physical_stats is a table valued function, it joins directly to sys.indexes, sys.tables, and sys.schemas the way the maintenance script above does, and feeds straight into a cursor, a report, or a scheduled job, none of which a console only command like SHOWCONTIG ever supported.

It is worth being explicit about why this lesson and the previous one sit apart from the DMV and Activity Monitor coverage earlier in this course, rather than folding into it. Fragmentation and corruption can both slow a database down or, in corruption's case, break it outright, but they are different kinds of problem with different detection tools and different fixes. sys.dm_exec_query_stats and Query Store, covered earlier, tell you a query got slower; they do not tell you whether that slowdown traces back to fragmentation, a bad execution plan, or something else entirely. sys.dm_db_index_physical_stats answers that specific question for the fragmentation case, the same way CHECKTABLE and CHECKDB answer a structurally different question, whether the data itself is actually intact, that no performance DMV is built to answer at all.

That division is also why both belong on their own independent schedules rather than being triggered only when a performance problem is already visible. A table can be badly fragmented, or carry a quietly corrupted page, while every performance counter this course has covered so far still looks perfectly healthy, right up until a large scan hits the fragmented pages or a query tries to read the corrupted one. Running CHECKTABLE or CHECKDB and checking fragmentation on a schedule, rather than only after something visibly breaks, is what catches both categories of problem while they are still cheap to fix.

The next lesson covers DBCC CHECKFILEGROUP and DBCC CHECKIDENT, the two remaining commands from this module's opening roadmap that have not been covered yet. DBCC DBREINDEX, the roadmap's last item, is deprecated legacy syntax for exactly what this lesson's maintenance script already does with ALTER INDEX, so it is likely to get the same brief, this-is-what-you-might-still-see treatment this lesson gave SHOWCONTIG, rather than a full section teaching it as current practice.