sysindexes

All databases

Description

sysindexes contains one row for each clustered index, one row for each nonclustered index, one row for each table that has no clustered index, and one row for each table that contains text or image columns.

Columns

The columns for sysindexes are:

Name	Datatype	Description
name	sysname	Index or table name
id	int	ID of a table, or ID of table to which index belongs
indid	smallint	0 if a table 1 if a clustered index on an allpages-locked table >1 if a nonclustered index or a clustered index on a data-only-locked table 255 if text, image or Java off-row structure (LOB structure)
doampg	int	Page number for the object allocation map of a table
ioampg	int	Page number for the allocation map of an index or (LOB structure)
oampgtrips	int	Number of times OAM pages cycle in the cache without being re-used, before being flushed
status2	smallint	Internal system status information (see Table 12-9)
ipgtrips	int	Number of times index pages cycle in the cache, without being reused, before being flushed
first	int	If indid is 0 or 1, page number of the first data page. If indid is between 2 and 250, page number of first leaf-level index page.
root	int	If indid is 0 and table is an unpartitioned allpages-locked table, page number of last page of page chain; unused for other types of pages. If indid is between 1 and 250, page number of root of index tree.
distribution	int	Unused. Formerly used to store the page number of the distribution page for an index.
usagecnt	smallint	Reserved
segment	smallint	Number of segment in which object resides
status	smallint	Internal system status information (see Table 12-9)
maxrowsperpage	smallint	Maximum number of rows per page
minlen	smallint	Minimum size of a row
maxlen	smallint	Maximum size of a row
maxirow	smallint	Maximum size of a non-leaf index row
keycnt	smallint	Number of keys for a clustered index on an allpages-locked table; number of keys, plus 1 for all other indexes
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
base_partition	int	Partition number, incremented by alter table...unpartition commands
fill_factor	smallint	Value for the fillfactor of a table set with sp_chgattribute
res_page_gap	smallint	Value for the reservepagegap on a table
exp_rowsize	smallint	Expected size of data rows
keys3	varbinary(255)	Description of key columns if entry is an index
identitygap	intn	Identity gap for a table
crdate	datetime	Creation date

Table 12-8 lists the bit representations for the status column.

**Table 12-8: Status bits in the sysindexes table status column**
Decimal	Hex	Status
1	0x1	Abort current command or trigger if attempt to insert duplicate key
2	0x2	Unique index
4	0x4	Abort current command or trigger if attempt to insert duplicate row; always 0 for data-only-locked tables
16	0x10	Clustered index
64	0x40	Index allows duplicate rows, if an allpages-locked table; always 0 for data-only-locked tables
128	0x80	Sorted object; not set for tables without clustered indexes or for text objects
512	0x200	sorted data option used in create index statement
2048	0x800	Index on primary key
32768	0x8000	Suspect index; index was created under another sort order

Table 12-9 lists the bit representations for the status2 column.

**Table 12-9: Status bits in the *sysindexes* table *status2* column**
Decimal	Hex	Status
1	0x1	Index supports foreign key constraint
2	0x2	Index supports primary key/unique declarative constraint
4	0x4	Index includes an IDENTITY column
8	0x8	Constraint name not specified
16	0x10	Large I/Os (prefetch) not enabled for table, index, or text chain
32	0x20	MRU cache strategy not enabled for table, index, or text chain
64	0x40	Ascending inserts turned on for the table
256	0x0100	Index is presorted and does not need to be copied to new extents
512	0x0200	Table is a data-only-locked table with a clustered index
8192	0x2000	Index on a data-only-locked table is suspect

Indexes

Unique clustered index On id, indid