Read Index Structure

I recently listened in on a conversation between SQL Server MVPs Cristian Lefter and our very own Itzik Ben-Gan.

Cristian asked, "Hi, I want to display an index structure from an AdventureWorks index an indexing chapter of a book.  I mean by that the content of the leaf level pages and the page numbers and some data for the leaf level pages.  How would you do that - DBCC IND and DBCC PAGE?"

I thought Itzik's response was quite a neat tip, so (with his permission) I'll share it here:

You can use the following code to generate a string with the page addresses in the leaf level in linked list order:

CREATE TABLE #DBCCIND(
    PageFID INT,
    PagePID INT,
    IAMFID INT,
    IAMPID INT,
    ObjectID INT,
    IndexID INT,
    PartitionNumber INT,
    PartitionID BIGINT,
    iam_chain_type VARCHAR(100),
    PageType INT,
    IndexLevel INT,
    NextPageFID INT,
    NextPagePID INT,
    PrevPageFID INT,
    PrevPagePID INT);

INSERT INTO #DBCCIND
EXEC ('DBCC IND(''AdventureWorks'', ''Sales.SalesOrderHeader'',-1)');

WITH LinkedList AS
( SELECT 1 AS RowNum, PageFID, PagePID FROM #DBCCIND WHERE IndexID 
    = 1 -- specify index id here
    AND IndexLevel = 0
    AND PrevPageFID = 0
    AND PrevPagePID = 0

UNION ALL

SELECT PrevLevel.RowNum + 1,
    CurLevel.PageFID, CurLevel.PagePID FROM LinkedList AS PrevLevel
    JOIN #DBCCIND AS CurLevel
    ON CurLevel.PrevPageFID = PrevLevel.PageFID
    AND CurLevel.PrevPagePID = PrevLevel.PagePID )

SELECT
CAST(PageFID AS VARCHAR(MAX)) + ':'
+ CAST(PagePID AS VARCHAR(MAX)) + ' ' AS [text()] FROM LinkedList 
ORDER BY RowNum FOR XML PATH('') OPTION (MAXRECURSION 0);

DROP TABLE #DBCCIND;

For example, here I requested the layout of the linked list in the clustered index of AdventureWorks.Sales.SalesOrderHeader, and got the following output for the clustered index (IndexID = 1):

    1:5280 1:5281 1:5282 ... 1:6024 1:6025 1:6026

And this is what I get for IndexID = 2:

    1:8184 1:8185 1:8186 ... 1:8335 1:8336 1:8337

Both indexes are non-fragmented and were created when the data existed so it seems that the page numbers keep increasing sequentially, but with indexes that have fragmentation the picture would naturally be different.

Anyway, you can then use DBCC PAGE to investigate the contents of particular pages.