SQL Server: Using DBCC IND to Find Data About One Index

The dbcc ind command is an undocumented command in SQL Server which allows you to see how data pages are organized and link to each other. It doesn’t drill down to the data itself. For that you need to use dbcc page. Use the DBCC Ind command to find the pages of interest, such as an index page then jump off to use the DBCC Page. One of the draw backs of the DBCC IND command is that it doesn’t allow you to differentiate between different indexes, since the results combine all the results together. The script below does just that. This is a dirty script that can be taken in several directions.

--Declare/Set DB, Table, Index
Declare @DBName varchar(200)
Declare @TableName Varchar(200)
Declare @IndexName Varchar(200)

--User Input Goes Here
Set @DBName = 'AdventureWorks'
Set @TableName = 'T1'
Set @IndexName = 'test'

--Build Command
Declare @Command varchar(100)
set @Command = 'DBCC ind(' + '''' + @DBName + ''''
+ ', ' + '''' + @TableName + '''' + ', '
+ '-1)'

--Get Index number
Declare @IndexCommand varchar(200)
Declare @Indexid int
Declare @Inthold table(indexid int)

Set @IndexCommand = 'select index_id from ' +
@DBName + '.sys.indexes where object_id = object_id(' +
'''' + @TableName + '''' + ') and Name = ' + '''' +
@IndexName + ''''

insert into @inthold
execute (@IndexCommand)

set @Indexid = (select top 1 indexid from @Inthold)


--Check Table Existence

IF OBJECT_ID('tempdb..#IndHold') IS NOT NULL
Drop table #IndHold

Create table #IndHold
(
PageFID int,
PagePID bigint,
IAMFID int,
IAMPID int,
ObjectID bigint,
IndexID int,
PartitionNumber int,
PartitionID bigint,
iam_chain_type varchar(50),
PageType int,
IndexLevel int,
NextPageFID int,
NextPagePID bigint,
PrevPageFID int,
PrevPagePID bigint)

Insert into #IndHold
exec(@Command)


--Get all Info for selected Index
Select * from #IndHold where IndexID = @Indexid
order by PartitionNumber, PageType desc, NextPagePid