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.This table also contains one row for each function-based index or index created on a computed column.

Columns

The columns for sysindexes are:

Name

Datatype

Description

name

varchar(255) null

Index or table name.

id

int

ID of an index, or ID of table to which index belongs.

indid

smallint

Valid values are:

  • 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, text chain, or Java off-row structure (large object—or LOB—structure).

doampg

int

Obsolete

ioampg

int

Obsolete

oampgtrips

int

Number of times OAM pages cycle in the cache without being reused, before being flushed

status3

smallint

Internal system status information.

status2

smallint

Internal system status information (see Table 1-13)

ipgtrips

int

Number of times index pages cycle in the cache, without being reused, before being flushed

first

int

Obsolete

root

int

Obsolete

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 1-12)

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) null

Description of key columns if entry is an index

keys2

varbinary(255) null

Description of key columns if entry is an index

soid

tinyint

Sort order ID with which the index was created; 0 if there is no character data in the keys

csid

tinyint

Character set ID with which the index was created; 0 if there is no character data in the keys

base_partition

int null

Obsolete

fill_factor

smallint null

Value for the fillfactor of a table set with sp_chgattribute

res_page_gap

smallint null

Value for the reservepagegap on a table

exp_rowsize

smallint null

Expected size of data rows

keys3

varbinary(255) null

Description of key columns if entry is an index

identitygap

int null

Identity gap for a table

crdate

datetime null

Creation date

partitiontype

smallint null

Values are:

  • 1 = range

  • 2 = hash

  • 3 or NULL = [default] round robin

  • 4 = list

conditionid

int null

ID of the partition condition. Null if partitiontype is round-robin or hash

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

Table 1-12: 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

Table is an all-pages-locked table with a clustered index.

64

0x40

Index allows duplicate rows, if an allpages-locked table; always 0 for data-only-locked tables.

128

0x80

Sorted object toggle that is being used internally. Can be set by create clustered index, reorg rebuild, or alter table locking scheme commands.

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 1-13 lists the bit representations for the status2 column.

Table 1-13: 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

Most recently used (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

32768

0x8000

The index is function-based

Indexes