Inspecting database file contents

Pages in a database are returned by the sys.dm_db_database_page_allocations DMF. Parameters:

• @databaseId – SMALLINT, mandatory
• @tableId – INT, optional (table's object_id; default is all tables in database)
• @indexId – INT, optional (default is all indexes)
• @partitionId – INT, optional (default is all partitions)
• @mode – mandatory; either 'DETAILED' OR 'LIMITED'

SELECT * 
FROM sys.dm_db_database_page_allocations(
  DB_ID(), OBJECT_ID('[dbo].[Test]'), NULL, NULL, 'DETAILED'
)
WHERE is_allocated = 1;

DBCC IND is available in earlier versions and returns a subset of the information in sys.dm_db_database_page_allocations. Parameters:

• database ID – SMALLINT, mandatory, literals only
• object ID/name – INT/NVARCHAR, mandatory, literals only
• index ID – INT, mandatory, literals only

DBCC IND(7, 'dbo.Test', 1) WITH TABLERESULTS

Correspondence to columns in sys.dm_db_database_page_allocations:

To cache DBCC IND output for reuse:

IF OBJECT_ID('tempdb..#dbccInd') IS NOT NULL
  DROP TABLE #dbccInd
 
CREATE TABLE #dbccInd (
  allocated_page_file_id SMALLINT         -- PageFID
, allocated_page_page_id INT              -- PagePID
, allocated_page_iam_file_id SMALLINT     -- IAMFID
, allocated_page_iam_page_id INT          -- IAMPID
, [object_id] INT                         -- ObjectID
, index_id INT                            -- IndexID
, [partition_id] INT                      -- PartitionNumber
, rowset_id BIGINT                        -- PartitionID
, allocation_unit_type_desc NVARCHAR(60)  -- iam_chain_type
, page_type INT                           -- PageType
, page_level TINYINT                      -- IndexLevel
, next_page_file_id SMALLINT              -- NextPageFID
, next_page_page_id INT                   -- NextPagePID
, previous_page_file_id SMALLINT          -- PrevPageFID
, previous_page_page_id INT               -- PrevPagePID
)
 
INSERT INTO #dbccInd
EXEC ('DBCC IND(7, ''dbo.Test'', 1) WITH TABLERESULTS')
 
--SELECT * FROM #dbccInd

Page contents are inspected using DBCC PAGE. Parameters:

• database ID/name – from sys.databases or DB_ID()
• file ID – for selected page as given in allocated_page_file_id (PageFID) above
• page number – for selected page as given in allocated_page_page_id (PagePID) above
• print option; one of:
  • 0 – page and buffer header fields
  • 2 – page and buffer header fields, whole page as bytes, slot directory (row offset table)
  • 1 – page and buffer header fields, each row as bytes, slot directory (row offset table)
  • 3 – page and buffer header fields, each row as bytes then as column values, slot directory (row offset table)

To print output to the console, enable trace flag 3604:

DBCC TRACEON (3604)
DBCC PAGE (7, 1, 289,2) 
DBCC TRACEOFF (3604)

To cache output in a table:

IF OBJECT_ID('tempdb..#dbccInd') IS NOT NULL
  DROP TABLE #dbccInd
 
CREATE TABLE #dbccInd (
  [ParentObject] VARCHAR(100)
, [Object] VARCHAR(1000)
, [Field] VARCHAR(100)
, [VALUE] VARCHAR(MAX)
);
 
INSERT INTO #dbccInd
EXEC sp_executesql N'DBCC PAGE (7, 1, 289,2) WITH TABLERESULTS'
 
--SELECT * FROM #dbccInd