Obtain 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:




Pointer to the allocation page for the text page chain


Pointer to the first page of text data


Pointer to the last page


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"
Note: The 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.