dbcc reindex checks the integrity of indexes on user tables by running a “fast” version of dbcc checktable. For details, see “dbcc checktable”. dbcc reindex drops and rebuilds the indexes where the sort order used is not consistent with the new sort order. When dbcc reindex discovers the first index-related error, it displays a message, and then rebuilds the inconsistent indexes. The System Administrator or table owner should run dbcc reindex after changing the sort order in Adaptive Server.
The syntax is:
dbcc reindex ({table_name | table_id})
Run this command on all tables listed by sp_indsuspect as containing suspect indexes. For example:
dbcc reindex(titles)
One or more indexes are corrupt. They will be rebuilt.
In the preceding example, dbcc reindex discovers one or more suspect indexes in the table titles; it drops and re-creates the appropriate indexes.
If the indexes for a table are already correct, or if there are no indexes for the table, dbcc reindex does not rebuild any indexes. It displays a message instead. If a table is suspected of containing corrupt data, the command is aborted. If that happens, an error message instructs the user to run dbcc checktable.
When dbcc reindex finishes successfully, all “suspect” marks on the table’s indexes are removed. The “read-only” mark on the table is also removed, and the table can be updated. These marks are removed whether or not any indexes have to be rebuilt.
dbcc reindex does not reindex system tables. System indexes are checked and rebuilt, if necessary, as an automatic part of recovery after Adaptive Server is restarted following a sort order change.
