-- Procedure for re-intitializing the DBCC IND() data
if object_id('zcpReinitDbccInd') > 0
	drop procedure zcpReinitDbccInd;
go
create procedure zcpReinitDbccInd 
	@tableName	nvarchar(500)
as
	declare @sql nvarchar(max);
	
	if isnull(object_id(@tableName),0) = 0 begin
		raiserror ('You have to specify a table that exists in @tableName. This doesn''t exist: [%s]',16,1,@tableName);
		return -2;
	end

	-- Table to hold dbcc ind() results...
	if object_id('ztblDbccInd') > 0
		drop table ztblDbccInd;
	-- Create
	create table ztblDbccInd (
		PageFID			tinyint, 
		PagePID			int,   
		IAMFID			tinyint, 
		IAMPID			int, 
		ObjectID		int,
		IndexID			tinyint,
		PartitionNumber	tinyint,
		PartitionID		bigint,
		iam_chain_type	varchar(30),    
		PageType		tinyint, 
		IndexLevel		tinyint,
		NextPageFID		tinyint,
		NextPagePID		int,
		PrevPageFID		tinyint,
		PrevPagePID		int
	);
	create unique clustered index ix_ztblDbccInd_PageFID_PagePID on ztblDbccInd (PageFID,PagePID,IndexID);

	set @sql = 'dbcc ind(' + db_name() + ', ' + quotename(@tableName) + ', -1) with no_infomsgs';
	
	-- Load...
	insert	ztblDbccInd
	exec	(@sql);

	-- Remove pages we aren't interested in...
	delete	ztblDbccInd
	where	PageType not in(1,2);

	-- Add the querying columns
	alter table ztblDbccInd add logicalPosition int;
	alter table ztblDbccInd add physicalPageDiff int;

	-- Fill the pageLinkage table...
	-- What this will do for us is give us the logical ordinal position for each page (i.e., the position along the linked
	-- list chain) at the leaf level of the heap/index...
	with pageLinkage (logicalPosition,physicalPageDiff,pageFID,pagePID,nextFID,nextPID,prevFID,prevPID,indexID) as (
		select	1 as logicalPosition, 0 as physicalPageDiff,
				i.PageFID, i.PagePID, i.NextPageFID, i.NextPagePID, i.PrevPageFID, i.PrevPagePID, i.IndexID
		from	ztblDbccInd i
		where	IndexLevel = 0
		and		PrevPageFID = 0
		and		PrevPagePID = 0
		and		NextPageFID > 0
		union all
		select	l.logicalPosition + 1, 
				case 
					-- This will ensure we don't falsly report as having physical fragmentation when we just have
					-- multiple files in the DB instead...
					when i2.PageFID <> l.pageFID then 0	
					when i2.PagePID > l.pagePID then ((i2.PagePID - l.pagePID) - 1) 
					else ((i2.PagePID - l.pagePID) + 1) 
				end as physicalPageDiff,
				i2.PageFID, i2.PagePID, i2.NextPageFID, i2.NextPagePID, i2.PrevPageFID, i2.PrevPagePID, i2.IndexID
		from	ztblDbccInd i2
		join	pageLinkage l
		on		i2.PageFID = l.nextFID
		and		i2.PagePID = l.nextPID
		and		i2.IndexID = l.indexID
		where	i2.IndexLevel = 0
	)
	update	i
	set		logicalPosition = pl.logicalPosition,
			physicalPageDiff = pl.physicalPageDiff
	from	ztblDbccInd i
	join	pageLinkage pl
	on		i.PageFID = pl.pageFID
	and		i.PagePID = pl.pagePID
	and		i.IndexID = pl.indexID
	option (maxrecursion 0);

	create nonclustered index ix_ztblDbccInd_show on ztblDbccInd (
		IndexID,
		logicalPosition,
		physicalPageDiff )
	include(
		PageFID,
		PagePID,
		NextPageFID,
		NextPagePID,
		PrevPageFID,
		PrevPagePID,
		PageType,
		IndexLevel);

	create nonclustered index ix_ztblDbccInd_show2 on ztblDbccInd (
		IndexID,
		physicalPageDiff,
		logicalPosition	)	
	include(
		PageFID,
		PagePID,
		NextPageFID,
		NextPagePID,
		PrevPageFID,
		PrevPagePID,
		PageType,
		IndexLevel);

go

-- Procedure for viewing the page linkage
if object_id('zcpShowPageLinkage') > 0
	drop procedure zcpShowPageLinkage;
go
create procedure zcpShowPageLinkage as
/*
	This will show a record for each page at the leaf-level of each index (or all data pages in a heap) sorted
	by the logical ordinal position of each page - the logical ordinal position meaning the order the page should
	fall in if you were to scan the data from front-to-back using an ordered-index scan of the data (doesn't apply
	for heaps, since they don't hold any logical order). If you see a value other than '0' in the physicalPageDiff
	column, this tells you that this page is that # of pages away from the prior page in the logical order, which 
	indicates that there are other pages of data between the 2 pages - if the value is negative, then that page is
	earlier in the file; if positive, that page is later in the file (which comes into play when performing a scan
	of data). 
*/
	select	IndexID,logicalPosition,physicalPageDiff,PageFID,PagePID,
			NextPageFID,NextPagePID,PrevPageFID,PrevPagePID,
			PageType, IndexLevel
	from	ztblDbccInd
	where	logicalPosition is not null
	order by IndexID, logicalPosition;

go

-- Procedure for viewing summary info on fragmentation
if object_id('zcpShowFragSummary') > 0
	drop procedure zcpShowFragSummary;
go
create procedure zcpShowFragSummary 
	@showFragPages	tinyint = 0
as
/*
	This will show 3 result sets. The 1st is a summary of the count of logically fragmented pages, file-level fragmented
	pages, non-contiguous pages, non-leaf pages, and the total # of pages for each index/heap in a given table. The 2nd
	shows each page that is logically fragmented, and earlier physically within the file than the prior page, 
	as well as the previous and next page logically ordered for comparison
	purposes (each logically unordered page will show a 'position' value of '00', wheras the previous page will have a 
	position value of '-1', and the next page will show a position value of '+1'). The 3rd result set is the same as the
	2nd, except it shows logically unordered pages that are later physically in the file than the prior page.
*/
	-- Show the summary totals...
	select	IndexID,
			sum(logicalFrag) as logicalFragCount,
			sum(fileFrag) as extentFragCount,
			sum(nonContiguous) as nonContiguousPageCount,
			sum(isIntPage) as nonLeafPageCount,
			count(*) as totalPages
	from	(
				select	case when i1.physicalPageDiff < 0 then 1 else 0 end as logicalFrag,
						case when i1.physicalPageDiff > 1 then 1 else 0 end as fileFrag,
						case 
							when i1.physicalPageDiff > 1 then 1
							when i1.physicalPageDiff < 0 then 1
							else 0
						end as nonContiguous,
						case when (i1.physicalPageDiff is null and IndexID > 0) then 1 else 0 end as isIntPage,
						IndexID
				from	ztblDbccInd i1
			) tmp	
	group by IndexID;

	if @showFragPages > 0 begin
		-- Show any logically fragmented data as well as the prev/next pages for each out of order page...
		with logicalFragPrevNext as (
			select	a.logicalPosition,a.physicalPageDiff,a.PageFID,a.PagePID,
					a.NextPageFID,a.NextPagePID,a.PrevPageFID,a.PrevPagePID,
					a.PageType, a.IndexLevel,
					'00' as position
			from	ztblDbccInd a
			where	physicalPageDiff < 0
			union all
			select	n.logicalPosition,n.physicalPageDiff,n.PageFID,n.PagePID,
					n.NextPageFID,n.NextPagePID,n.PrevPageFID,n.PrevPagePID,
					n.PageType, n.IndexLevel,
					case when n.logicalPosition = l.logicalPosition + 1 then '+1' else '-1' end as position
			from	logicalFragPrevNext l
			join	ztblDbccInd n
			on		(n.logicalPosition = l.logicalPosition + 1
					or n.logicalPosition = l.logicalPosition - 1)
			and		l.position = '00'
		)
		select	*
		from	logicalFragPrevNext
		order by logicalPosition;

		-- Show any extent fragmented data (not counting those that match the logical frag data above) as
		-- well as the prev/next pages for each out of order page...this is basically out-of-order
		-- pages at the file level moving forward only (i.e. no pages will show here that are non-contiguous but
		-- move backwards in the file, those will show up above, even though they do technically qualify as 
		-- extent fragementation, I won't include them just to keep out dups in the lists)...
		with fileFragPrevNext as (
			select	a.logicalPosition,a.physicalPageDiff,a.PageFID,a.PagePID,
					a.NextPageFID,a.NextPagePID,a.PrevPageFID,a.PrevPagePID,
					a.PageType, a.IndexLevel,
					'00' as position
			from	ztblDbccInd a
			where	physicalPageDiff > 1
			union all
			select	n.logicalPosition,n.physicalPageDiff,n.PageFID,n.PagePID,
					n.NextPageFID,n.NextPagePID,n.PrevPageFID,n.PrevPagePID,
					n.PageType, n.IndexLevel,
					case when n.logicalPosition = l.logicalPosition + 1 then '+1' else '-1' end as position
			from	fileFragPrevNext l
			join	ztblDbccInd n
			on		(n.logicalPosition = l.logicalPosition + 1
					or n.logicalPosition = l.logicalPosition - 1)
			and		l.position = '00'
		)
		select	*
		from	fileFragPrevNext
		order by logicalPosition;
	end

go