For SQL Server 6.5 information, see sysindexes System Table in What's New for SQL Server 6.5.
Contains one row for each clustered index and one row for each nonclustered index. These indexes are the result of a CREATE INDEX statement or the CREATE TABLE statement with a PRIMARY KEY or UNIQUE constraint. Additionally, sysindexes contains one row for each table that has no clustered index and one row for each table that contains text or image columns.
| Column | Datatype | Description |
|---|---|---|
| name | varchar(30) | Name of table (for indid = 0 or 255). Otherwise, name of index. |
| id | int | ID of table (for indid = 0 or 255). Otherwise, ID of table to which the index belongs. |
| indid | smallint | ID of index: 0 Table 1 Clustered index >1 Nonclustered 255 Entry for tables that have text or image data |
| dpages | int | For indid = 0 or indid = 1, dpages is the count of used data-only pages. For indid = 255, rows is set to 0. Otherwise, dpages is the count of leaf-level index pages. |
| reserved | int | For indid = 0 or indid = 1, reserved is the total of pages allocated for all indexes on the table and the data pages. For indid = 255, reserved is the total pages allocated for text or image data. Otherwise, reserved is the total count of pages allocated only for this index. |
| used | int | For indid = 0 or indid = 1, used is the total count of pages used for all indexes on the table and the data pages. For indid = 255, used is the total pages used for text or image data. Otherwise, used is the total count of pages used only for this index. |
| rows | int | The data-level row count based on indid = 0 or indid = 1. This value is repeated for indid > 1. For indid = 255, rows is set to 0. |
| first | int | Pointer to first data or leaf page. |
| root | int | For indid >= 1 and < 255, root is the pointer to the root page. For indid = 0 or indid = 255, root is the pointer to the last page. |
| distribution | int | Pointer to distribution page (if entry is an index). |
| OrigFillFactor | tinyint | The original fillfactor value used when the index was created. This value is not maintained; however, it can be helpful if you need to re-create an index and do not remember what fillfactor was used. |
| segment | smallint | Number of segment in which this object resides. |
| status | smallint | Internal system-status information: 1 Cancel command if attempt to insert duplicate key 2 Unique index 4 Cancel command if attempt to insert duplicate row 16 Clustered index 64 Index allows duplicate rows 2048 Index used to enforce PRIMARY KEY constraint 4096 Index used to enforce UNIQUE constraint |
| rowpage | smallint | Maximum count of rows per page. |
| minlen | smallint | Minimum size of a row. |
| maxlen | smallint | Maximum size of a row. |
| maxirow | smallint | Maximum size of a nonleaf index row. |
| keycnt | smallint | Number of keys. |
| keys1 | varbinary(255) | Description of key columns (if entry is an index). |
| keys2 | varbinary(255) | Description of key columns (if entry is an index). |
| soid | tinyint | Sort order ID that the index was created with. 0 if there is no character data in the keys. |
| csid | tinyint | Character set ID that the index was created with. 0 if there is no character data in the keys. |
sysindexes clustered, unique on id, indid
| sp_dropsegment | sp_helplog | sp_rename |
| sp_fkeys | sp_helpsegment | sp_spaceused |
| sp_helpconstraint | sp_pkeys | sp_special_columns |
| sp_helpindex | sp_placeobject | sp_statistics |