sysindexes

Transact-SQL Reference

Transact-SQL Reference

sysindexes

Contains one row for each index and table in the database. This table is stored in each database.

Column name Data type Description
id int ID of table (for indid = 0 or 255). Otherwise, ID of table to which the index belongs.
status int Internal system-status information.
first binary(6) Pointer to the first or root page.
indid smallint ID of index:

1 = Clustered index
>1 = Nonclustered
255 = Entry for tables that have text or image data

root binary(6) 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.
minlen smallint Minimum size of a row.
keycnt smallint Number of keys.
groupid smallint Filegroup ID on which the object was created.
dpages int For indid = 0 or indid = 1, dpages is the count of data pages used. For indid=255, it is set to 0. Otherwise, it is the count of index pages used.
reserved int For indid = 0 or indid = 1, reserved is the count of pages allocated for all indexes and table data. For indid = 255, reserved is a count of the pages allocated for text or image data. Otherwise, it is the count of pages allocated for the index.
used int For indid = 0 or indid = 1, used is the count of the total pages used for all index and table data. For indid = 255, used is a count of the pages used for text or image data. Otherwise, it is the count of pages used for the index.
rowcnt bigint Data-level rowcount based on indid = 0 and indid = 1. For indid = 255, rowcnt is set to 0.
rowmodctr int Counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table.
xmaxlen smallint Maximum size of a row.
maxirow smallint Maximum size of a nonleaf index row.
OrigFillFactor tinyint 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.
reserved1 tinyint Reserved.
reserved2 int Reserved.
FirstIAM binary(6) Reserved.
impid smallint Reserved. Index implementation flag.
lockflags smallint Used to constrain the considered lock granularities for an index. For example, a lookup table that is essentially read-only could be set up to do only table level locking to minimize locking cost.
pgmodctr int Reserved.
keys varbinary(816) List of the column IDs of the columns that make up the index key.
name sysname Name of table (for indid = 0 or 255). Otherwise, name of index.
statblob image Statistics BLOB.
maxlen int Reserved.
rows int Data-level rowcount based on indid = 0 and indid = 1, and the value is repeated for indid >1. For indid = 255, rows is set to 0. Provided for backward compatibility.