====== Inspecting database file contents ======

===== Database pages =====
==== SQL Server 2012 and beyond ====

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'

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

==== Before SQL Server 2012 ====

''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**

<code tsql>DBCC IND(7, 'dbo.Test', 1) WITH TABLERESULTS</code>

Correspondence to columns in ''sys.dm_db_database_page_allocations'':
^''DBCC IND'' column name  ^Returned in ''sys.dm_db_database_page_allocations'' as   ^
|PageFID  |allocated_page_file_id  |
|PagePID  |allocated_page_page_id  |
|IAMFID  |allocated_page_iam_file_id  |
|IAMPID  |allocated_page_iam_page_id  |
|ObjectID  |object_id  |
|IndexID  |index_id  |
|PartitionNumber  |partition_id  |
|PartitionID  |rowset_id  |
|iam_chain_type  |allocation_unit_type_desc  |
|PageType  |page_type  |
|IndexLevel  |page_level  |
|NextPageFID  |next_page_file_id  |
|NextPagePID  |next_page_page_id  |
|PrevPageFID  |previous_page_file_id  |
|PrevPagePID  |previous_page_page_id  |

To cache ''DBCC IND'' output for reuse:
<code tsql>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</code>

===== Page contents =====

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:

<code tsql>DBCC TRACEON (3604)
DBCC PAGE (7, 1, 289,2) 
DBCC TRACEOFF (3604)</code>

To cache output in a table:

<code tsql>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</code>

{{tag> tsql admin}}