The sysindexes system table contains information about your index structures that you can't get through information schema views. Every database has a sysindexes table, which contains one row for every index and one row for every column-based statistics on a table. In general, Microsoft recommends that you don't retrieve data directly from this table, but there's no other way to obtain some of the information it contains. You can derive some of the index properties from the INDEXPROPERTY() function, but not all of them, as I mentioned in my December column, "Digging Up the Dirt on Indexes," InstantDoc ID 44002.
One type of information that you can get from the sysindexes table and from no other documented method is certain page numbers that a table or index uses. The sysindexes table contains three columns—first, firstIAM, and root—that represent page locations within a database, as indicated by a file number/page number combination. SQL Server stores each of these page locations in a byte-swapped format. To convert this to a decimal file number and page number, you must first swap the bytes, then convert the values from hexadecimal to decimal. For example, let's find the page address for the first page of the Northwind database's Order Details table. You can run the following query to get the value in hex:
USE Northwind
SELECT first FROM sysindexes
WHERE id = object_id('Order Details') AND indid < 2
As I mentioned in December, a sysindexes.indid value of less than 2 identifies the row for the table itself and not for a separate index on the table.
The preceding query returns the hex value 0x940000000100 for the first page of the table. You now have to convert this number to a file and page address. In hexadecimal notation, each set of two hexadecimal digits represents 1 byte, so each pair of digits has to stay together as a byte. You first "swap" or reverse the bytes, so the last becomes the first and the first becomes the last, to get 00 01 00 00 00 94. The first two groups represent the 2-byte file number, and the last four groups represent the page number. So the file number is 0x0001, which is 1, and the page number is 0x00000094, which is 148 in decimal. This means that the first page of the Order Details table is on file 1, page 148. But in a relational database, there's no implied ordering to table rows, so what does "first" mean? "First" means something only if you have an index, in which case the index's leaf level stores the pages in an order that's based on the index key columns. For a clustered index, such as the one here, the index's leaf level is the table itself.
SQL Server doesn't guarantee that the sysindexes.first column will always tell you the first page of a table. I've found that the value is reliable immediately after I build an index and stays reliable until I perform a lot of data-modification operations on the data in the table. As I mentioned, two other columns in sysindexes use the same byte-swapped hexadecimal format. The root column's value is the page location of the index's root. If the table's row in sysindexes has an indid value of 0, the table is a heap and has no clustered index and consequently no root. In that case, the value in the sysindexes.root column is just a copy of the value in the sysindexes.first column.
The third hexadecimal column, first-IAM, is the page number of the first Index Allocation Map (IAM) for the table or index. You can get lots of information about IAMs in my April 1999 article, "The New Space Management," InstantDoc ID 5110.
You can use the page addresses for the first and root columns as arguments to the DBCC PAGE command, which I described in "Using DBCC Page," March 2000, InstantDoc ID 8097. If you want to know the page numbers for all the pages that make up a table, you could get the first page number from the sysindexes table as I described above, then use DBCC PAGE to look at the header of the page and find the value for m_nextPage. Then, you could use DBCC PAGE to open that next page, find the value for m_nextPage again, and continue to follow the chain of pages for any table that has a clustered index. However, as much as this method might appeal to your inner geek, there are easier ways to get all the page numbers belonging to a table or index. These methods, which I describe next, will always show you the correct page numbers in a table, even after you perform numerous data-modification operations.
First, enable the undocumented DBCC command PGLINKAGE by turning on trace flag 3604, just like you need to do for DBCC PAGE. In order to use this command, you need to know at least one page number belonging to the table. Then you pass the database ID, the file ID, the page ID, and the number of pages you want to see:
DBCC PGLINKAGE (dbid, fileID,
pagenum, number_of_pages, printopt={1|2} )
Supplying a 0 for number_of_pages will return all the pages from the given page number to the end of the linked list of pages. A printopt value of 1 lists just the file and page numbers; when you give a printopt value of 2, you can see the previous and next page numbers along with each page number. Thus, the following command will list the first 10 pages in the Northwind database's Order Details table:
DBCC TRACEON(3604)
DBCC PGLINKAGE (6,1,148,10, 1)
Prev. page
[1]
2
next page 
