Finding the pages allocated to a table

This is a useful procedure to display the pages allocated to a table using the DBCC IND statement

if object_id('up_ShowIndexPageOrder') is null

    exec ('create procedure up_ShowIndexPageOrder as print ''not implemented''')

go

alter procedure up_ShowIndexPageOrder

  @index varchar(100)

as

declare @sql varchar(max) = 'dbcc ind(' + char(39) + db_name() + char(39) + ',' + char(39) + @Index +char(39) + ',1)'

 

create table #Ind

(

PageFID  bigint,

PagePID  bigint,

IAMFID   bigint,

IAMPID   bigint,

ObjectID bigint,

IndexID  bigint,

PartitionNumber  bigint,

PartitionID  bigint,

iam_chain_type   varchar(100),

PageType bigint,

IndexLevel   bigint,

NextPageFID  bigint,

NextPagePID  bigint,

PrevPageFID  bigint,

PrevPagePID bigint)

 

insert into #ind execute(@sql)

 

select  i.PagePID , i.NextPagePID , i.NextPagePID - i.PagePID OffsetToNextPage, iam_chain_type ,IndexLevel , IAMFID ,IAMPID

from #ind i

 

drop table #Ind

Published Saturday, October 23, 2010 9:51 PM by simonsabin

Comments

Saturday, October 23, 2010 10:35 PM by Simons SQL Blog

# Shrinking data files - Things you shouldn’t do with SQL

This is the first in a follow up from my SQLBits and DDD sessions. Why is shrinking files bad? Shrinking

Saturday, October 23, 2010 10:43 PM by SimonS Blog on SQL Server Stuff

# Finding the pages allocated to a table

This is a useful procedure to display the pages allocated to a table using the DBCC IND statement if