Medusa Mirror - look at a table by using DBCC IND and DBCC PAGE to view it indirectly

look_at_table_indirectly

DECLARE @page_command VARCHAR(4000), @ind_command VARCHAR(4000), @dbid VARCHAR(5)
		, @pagefid varchar(50), @pagepid varchar(50)
DECLARE @database_name sysname, @table_name sysname, @has_clustered_index VARCHAR(2)
SET @database_name = 'mydbname'
SET @table_name = 'mytablename'
SET @has_clustered_index  = '1'
--assumes it has one, to make the DBCC IND run faster.  If you don't care, set to -1. 
--You could set it to an actual index page, but you have to know the ID

SELECT @dbid = DB_ID(@database_name)

IF object_id('tempdb..#temp_ind') is not null
	BEGIN 
    DROP TABLE #temp_ind
	END 
CREATE TABLE #temp_ind
(PageFID  BIGINT, PagePID BIGINT, IAMFID BIGINT, IAMPID BIGINT, ObjectID BIGINT, IndexID INT, 
	PartitionNumber INT, PartitionID bigint, iam_chain_type VARCHAR(50), PageType SMALLINT, 
	IndexLevel SMALLINT, NextPageFID BIGINT, NextPagePID BIGINT, PrevPageFID BIGINT, PrevPagePID BIGINT)

IF object_id('tempdb..#temp_page') is not null
	BEGIN 
    DROP TABLE #temp_page
	END 
CREATE TABLE #temp_page (ParentObject VARCHAR(100), [Object] VARCHAR(100), [field] VARCHAR(255), [value] VARCHAR(100))

SET @ind_command = 'DBCC IND(' + @dbid + ',' + @table_name + ',' + @has_clustered_index + ')'

INSERT INTO #temp_ind EXEC(@ind_command)

SELECT TOP 1 @PageFID = PageFID, @PagePID = PagePID FROM #temp_ind WHERE iamfid IS NOT NULL ORDER BY PagePID DESC 

SET @page_command = 'DBCC PAGE(' + @dbid + ',' + @pagefid + ',' + @pagepid + ',3) WITH TABLERESULTS'

INSERT INTO #temp_page EXEC (@page_command)

--Now pivot the data from a page to view some sample records
DECLARE @SQL as VARCHAR (MAX)
DECLARE @Columns AS VARCHAR (MAX)

SELECT @Columns= 
       COALESCE(@Columns + ',','') + QUOTENAME(field)
FROM
(
       SELECT DISTINCT field, 
	   SUBSTRING([Object], CHARINDEX('Column ',[Object])+7, CHARINDEX(' Offset ',[Object]) - CHARINDEX('Column ',[Object])-7) AS field_num
       FROM #temp_page WHERE [Object] LIKE '%Column%Offset 0x%'
) AS B
ORDER BY B.field_num

SET @SQL='
SELECT ParentObject, ' + @Columns + '  FROM 
(
SELECT ParentObject, field, value FROM 
#temp_page WHERE Object LIKE ''%column%offset%'' ) AS source
PIVOT
(max(value) FOR source.field IN (' + @columns + ')
)AS pvt'

EXEC (@sql)