Saturday, October 04, 2008

sysindexes (T-SQL)

Contains one row for each index and table in the database. This table is stored in each database. This same table can be used to identify clustered indexes and non-clustered indexes the clustered index always has the indid = 1 and a non-clustered index will have an indid > 1

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:
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.
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 <>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 binary(8) Data-level rowcount based on indid = 0 and indid = 1, and the value is repeated for 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.
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.
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 Used to track the number of pages that have changed in an index.
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.

2 comments:

Anonymous said...

Worth noting to any readers that the sysindexes given here is pre ASE 15; status column values for sysindexes in 15 has changed, notably status&16 to show clustered index doesn't work any more (nor indid=1). Have only just realised this myself!
Bob

Anonymous said...

Scrub that, looks like this could actually be from an MSSQL Server.