Getting information from sysindexes

Each table with text, unitext, or image columns has an additional row in sysindexes that provides information about these columns. The name column in sysindexes uses the form “tablename.” The indid is always 255. These columns provide information about text storage:

Table 1-18: Storage of text and image data

Column

Description

ioampg

Pointer to the allocation page for the text page chain

first

Pointer to the first page of text data

root

Pointer to the last page

segment

Number of the segment where the object resides

You can query the sysindexes table for information about these columns. For example, the following query reports the number of data pages used by the blurbs table in the pubs2 database:

select name, data_pages(db_id(), object_id("blurbs"), indid) 
    from sysindexes
    where name = "tblurbs"

NoteThe system tables poster shows a one-to-one relationship between sysindexes and systabstats. This is correct, except for text and image columns, for which information is not kept in systabstats.