-- Data page structure and dbcc page.
Author Nigel Rivett

This is a method of retrieving the the data page which stores the data in a table.
It can sometimes be useful to see what effect an operation has on the physical storage structures.
It gives the usage of "dbcc page", how to decifer first in sysindexes to obtain data page and the format of the page.

-- create a table to test with
create table test (s char(10), t varchar(10))
insert test select 'abc', 'def'
insert test select 'ghi', 'jkl'
insert test select 'ghi', null

/*
Now read the data page.
format for dbcc page is
dbcc page (dbname/id, file number, page number, option) - option = 2 for headers
the file number and page number for the table can be found from column "first" in the root entry in sysindexes.
*/
select * from sysindexes where id = object_id('test') and indid in (1,0)
/*
first = 0xB64738000100
the first four bytes are the page numver - the last two the file number
These entries are byte reversed and must be converted to integers for the dbcc page command
so
PageNum = 003847B6
FileNum = 0001
*/

declare @first binary(6)
select @first = first from sysindexes where id = object_id('test') and indid = 0
declare @PageNum int
select @PageNum = convert(int, substring(@first,4,1) + substring(@first,3,1) + substring(@first,2,1) + substring(@first,1,1) )
declare @FileNum int
select @FileNum = convert(int, substring(@first,6,1) + substring(@first,5,1))
select @FileNum, @PageNum

/*
now we can run the dbcc page command to get the data page of the table.
*/
declare @sql varchar(1000)
select @sql = 'dbcc page (''' + db_name() + ''', ' + convert(varchar(10),@FileNum) + ', ' +  convert(varchar(10),@PageNum) + ', 2)'
select @sql
dbcc traceon(3604)
exec (@sql)