How to Use DBCC CHECKTABLE Command in SQL Server Database?

Written By Kumar Raj
Anuraag Singh
Approved By Anuraag Singh
Published On December 6th, 2023
Reading Time 7 Minutes Reading

dbcc checktable syntax

Multiple T-SQL queries are available in SQL for users to eliminate endless errors. In this article, we’re going to have a look at the DBCC CHECKTABLE command in SQL Server. Here, all the details regarding this command like uses, benefits, drawbacks, syntax, etc are mentioned. What this command does is just execute the integrity checks for the pages of a given SQL table. Moreover, it has the capacity to fix errors for a given table as well.

Also, as we have mentioned what to do if DBCC CHECKDB Job failed in SQL, the Plan B for this DBCC CHECKTABLE command is also there in the end. Let’s begin the article with the overview of this command.

DBCC CHECKTABLE SQL Server – Brief Explanation

We all are aware of the CHECKDB command, but here there’s a slight change. This command focuses mainly on tables rather than a complete database. This command can easily fix corruption issues after checking the integrity of pages in SQL table. It can fix damaged tables with deleted records across MDF & NDF files.

Now, let’s have a look at the DBCC CHECKTABLE syntax in detail:

DBCC CHECKTABLE ( 'name_of_table' | 'name_of_view'
        [ , NOINDEX
            | index_id
                    [ , [ TABLOCK ] ]
                    [ , [ ESTIMATEONLY ] ]
                    [ , [ PHYSICAL_ONLY ] ]

Important Note: Must note that only one table at a time can be handled by this command. Also, it will not work on data objects in the database like stored procedures, triggers, views, rules, etc.


We have already understood the basic difference. Another reason for users to opt for the DBCC CHECKTABLE command is to address the entire table errors with utmost accuracy. This is not possible with the other one when we look for an entire database.

However, now, it’s time to find out the major difference that users get in this command. Therefore, we have mentioned that this DBCC CHECKTABLE syntax focuses on a table of the below basis:

  • Find if the Index, as well as data pages, are linked correctly to each other or not.
  • Check if the rows of a table have an equivalent row in the nonclustered index. 
  • Check if there is any consistency in the Pointers present.
  • The data validation on each page is responsible as well.
  • Verifying the sort order of indexes. If it’s correct or not.

It also checks if the partitioned table of index possesses a row & if it’s in the right partition or not. Moreover, it cross-checks the link level consistency among the file system & the table.

Now, to learn how to use DBCC CHECKTABLE cmd, we need to check for corruption first in the table.

How Can A User Find Out the Corruption of A Table?

There are bad pages also known as SUSPECT PAGES in the SQL Server database. The tables of suspect pages are located in the MSDB database. To find out the bad pages here, simply run the below command:

SELECT * From msdb.dbo.suspect pages

There is a problem with this command that users must be aware of. Pages that the database identified as damaged will be detected by this command. This means, all the pages with corruption issues might not be detected by this command.

Therefore, in that case, users need to run the DBCC CHECKTABLE T-SQL CMD.

DBCC CHECKTABLE Command All Consistency Checks

There are several combinations of this command that a user can use to check the table consistency. It can be helpful for users in different scenarios. Below are all six variations of this command mentioned for different use cases.

  1. Data Page Integrity Check – The Basic One

This is the most basic one & simply checks the data integrity of an individual table in a database.

USE Database1
  1. Logical Consistency Check – On Indexes

If users have not mentioned the NOINDEX clause, this command can easily check both the logical as well as physical consistency of a single table along with the nonclustered index.

USE Database1

Users must note that for indexed view, XML index, and spatial indexes, this command only performs the physical checks.

  1. Time for the Physical Consistency Check

In case, users run this DBCC CHECKDB command with the PHYSICAL_ONLY option, any logical checks will not take place. This is beneficial to cut down the run time and resource usage of the command. It mainly helps users find the torn pages, checksum failures, and common hardware failures present.

USE Database1

Tip: Make sure that with the physical-only option, users can not run the repair command. It just helps users to overcome the drawbacks of the logical check command.

  1. Integrity Consistency Check With NOINDEX Clause

This command does not perform in-depth checks for the non-clustered indexes.

USE Database1
  1. Data Integrity Consistency Check With Lock Option

This command can help users apply a shared table lock on the SQL database individual table rather than utilizing the internal database snapshot:

USE Database1
  1. Consistency Check for a Particular Index

In case users want to check a particular index on the basis of their suspect, they can use this command:

USE Database1
DECLARE @IndexId int;
SET @IndexId = (SELECT index_id
              FROM sys.indexes
              WHERE object_id = OBJECT_ID('Table1')
                    AND name = 'Index_Table1');
DBCC CHECKTABLE ('Table1',@IndexId);

How to Use DBCC CHECKTABLE SQL Server to Solve Errors

Just like the CHCEKDB option, here, users get all three options of Repair_Rebuild, Repair_Fast, & Repair_Allow_Data_Loss. Users can select any of these to execute SQL Server repair table task. We are going with the Repair_Rebuild option here.

Run the below command:

USE Database1

Now, for the Repair_Allow_Data_Loss & Repair_Fast options,  users need to run the same command with just one change. Just replace the repair option with the preferred one. However, we all are aware of the fact that these commands do not always work properly. Therefore, we have a backup plan as well.

What If DBCC CHECKTABLE Command Fails?

In case, users fail to get the desired results after running this command, they can simply trust the automated solution from SysTools known as the SQL Recovery Tool which is the best alternative to DBCC CHECKTABLE syntax. This tool can easily fix an individual table, multiple tables, & even an entire database with ease. Download the Tool now, to get the best results.

Download Tool Purchase Now

The Final Say

After analyzing all of the crucial aspects here, users can easily understand the importance of this DBCC CHECKTABLE in SQL Server. We have explained in detail how to use this CHECKTABLE command syntax to get the maximum benefits. Moreover, users can even try the automated solution if they find it as a better alternative to the manual command.


By Kumar Raj

A versatile writer with the vast knowledge of technology helps to reduce the gap between a user and technology. Provides easy and reliable ways to resolve multiple technical issues, which users encounter in their day-to-day life.