Retrieving Database Page Metadata

You can use DBCC PAGE command to retrieve meta-data about a database page. Before executing DBCC PAGE you need to execute DBCC TRACEON (3604) in order to return the output to the screen.

It is useful to look into the database pages.

Syntax for using DBCC PAGE is

dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ] )

The printopt parameter has the following meanings: (each option provides differing levels of information)

• 0 - print just the page header
• 1 - page header plus per-row hex dumps and a dump of the page slot
• 2 - page header plus whole page hex dump
• 3 - page header plus detailed per-row interpretation

Now let’s start with the Page Detail of a database table object.

CREATE TABLE EmployeeTable

(

emp_id INT IDENTITY(1,1),

first_name VARCHAR(50) NOT NULL,

last_name VARCHAR(50) NOT NULL,

hired_date DATETIME NOT NULL,

active BIT NOT NULL DEFAULT 1,

CONSTRAINT PK_EMPLOYEE PRIMARY KEY (emp_id),

)

Go

insert into EmployeeTable (first_name,last_name,hired_date)

select 'Pragyan', 'Mohanty', '1999-03-15'

go

insert into EmployeeTable (first_name,last_name,hired_date)

select 'Swagatika', 'Patnaik', '2001-07-05'

go

insert into EmployeeTable (first_name,last_name,hired_date)

select 'Sagarika', 'Das', '2002-11-10'

go

Now to check the Page detail for this newly created table we need to find the file Id and the Page Number, which we can get by using DBCC IND command.

 DBCC IND ( {dbname}, {table_name},{index_id} )

Where index_id, we can get from querying sys.indexs, -1 shows all indexes

DBCC IND ( 'testDemo', 'EmployeeTable' , -1 )

PageFID PagePID IAMFID IAMPID ObjectID    IndexID iam_chain_type  PageType

------- ------- ------ ------ ----------- ------- --------------- --------

1       1725    NULL   NULL   290100074   1       In-row data     10      

1       1724    1      1725   290100074   1       In-row data     1       

(2 row(s) affected)

Note: Page Type 1 means Data Page and Type 10 means Index Allocation Map

So from here we have got File Id = 1 and Page Id = 1724

Now we can DBCC PAGE Command to see the detail of Page and Storage information.

Before we can run DBCC PAGE, it's required that trace flag 3604 be set to instruct the engine to send output to the console; otherwise you won't see anything!

DBCC TRACEON(3604)

DBCC PAGE ('testDemo', 1, 1724, 1);

You can use DBCC PAGE ('testDemo', 1, 1724, 1) WITH TABLERESULTS; to get your result in table format.

By executing the DBCC PAGE you will get the result of Page Header, Data and OFFSET Table / Array.

Reference: http://technet.microsoft.com/

Enjoy code like you gotta dance when there's nobody watching ;)

Post Reference: Vikram Aristocratic Elfin Share & http://technet.microsoft.com/