sysindexes

Provides information about indexes and table storage structures, including clustered and nonclustered indexes and tables that contain text or image columns.

Name	Datatype	Description
name	varchar(255) null	An index or table name.
id	int	The index or table ID to which the index belongs.
indid	smallint	The ID that specifies which index or data storage structure the row represents for a table. The possible values are: 0 – A table. 1 – A clustered index on an allpages-locked table. > 1 – A nonclustered index or clustered index on a data-only-locked table. 255 – A text, image, text chain, or Java off-row structure (LOB).
doampg	int	Obsolete.
ioampg	int	Obsolete.
oampgtrips	int	The number of times OAM pages cycle through the cache without reuse before being flushed.
status3	smallint	Internal system status information.
status2	smallint	Internal system status information.
ipgtrips	int	The number of times index pages cycle through the cache without reuse before being flushed.
first	int	Obsolete.
root	int	Obsolete.
distribution	int	Not used. Previously stored the page number of the distribution page for an index.
usagecnt	smallint	Reserved for future use.
segment	smallint	The segment in which the object is stored.
status	smallint	Internal system status information.
maxrowsperpage	smallint	The maximum number of rows allowed per page.
minlen	smallint	The minimum row size in bytes.
maxlen	smallint	The maximum row size in bytes.
maxirow	smallint	The maximum size of a non-leaf index row.
keycnt	smallint	The number of keys for a clustered index on an allpages-locked table. For other indexes, the value is calculated as the number of keys plus 1.
keys1	varbinary(255) null	The description of key columns if the entry is an index.
keys2	varbinary(255) null	The description of key columns if the entry is an index.
soid	tinyint	The sort order ID used when the index was created. 0 – The index keys contain no character data. A sort order is not required.
csid	tinyint	The index keys contain no character columns. No character set applies.
base_partition	int null	Obsolete.
fill_factor	smallint null	The fill factor of a table, as set with sp_chgattribute.
res_page_gap	smallint null	The reserve page gap for a table.
exp_rowsize	smallint null	The expected size of data rows.
keys3	varbinary(255) null	The description of key columns if the entry is an index.
identitygap	int null	The identity gap for a table.
crdate	datetime null	The date and time when the object was created.
partitiontype	smallint null	A partition type. The possible values are: 1 – A range. 2 – A hash. 3 or NULL – Round-robin (default). 4 – List.
conditionid	int null	The partition condition ID. NULL – The partition type is round-robin or hash.