sp_listsuspect

Lists all indexes in a database that are currently offline because of corruption detected on recovery.

Syntax

sp_listsuspect_object [dbname]

Example 1 – Lists the suspect indexes in the current database:
```
sp_listsuspect_object
```
Example 2 – Lists the suspect indexes in the pubs2 database:
```
sp_listsuspect_object pubs2
```

There are additional considerations when using sp_listsuspect_object:

If an index on a data-only-locked table has suspect pages, the entire index is taken offline during recovery. Offline indexes are not considered by the query optimizer.
Use the system procedure sp_forceonline_object to bring an offline index online for repair.
Indexes on allpages-locked tables are not taken completely offline during recovery; only individual pages of these indexes are taken offline. These pages can be brought online with sp_forceonline_object.
sp_listsuspect_object lists the database name, object ID, object name, index ID, and access status for every suspect index in the specified database or, if dbname is omitted, in the current user database.
A value of SA_ONLY in the access column means that the index has been forced online for system administrator use only. A value of BLOCK_ALL means that the index is offline for everyone.

See the System Administration Guide for more information on recovery fault isolation.

Any user can execute sp_listsuspect_object. Permission checks do not differ based on the granular permissions settings.

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect