-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Written by Kimberly L. Tripp, SQLskills.com
-- All Rights Reserved.
-- 
-- For more scripts, sample code and additional training resources,
-- go to http://www.SQLskills.com
--	Immerse yourself in SQL Server
--
-- Disclaimer - Thoroughly test this script, execute at your own risk.
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


--USE dbname
go
CREATE TABLE #ShowContigOutput
(
	ObjectName				sysname,
	ObjectId				int,
	IndexName				sysname,
	IndexId					tinyint,
	Level					tinyint,
	Pages					int,
	Rows					bigint,
	MinimumRecordSize		smallint,
	MaximumRecordSize	smallint,
	AverageRecordSize		smallint,
	ForwardedRecords		bigint,
	Extents					int,
	ExtentSwitches			numeric(10,2),
	AverageFreeBytes		numeric(10,2),
	AveragePageDensity		numeric(10,2),
	ScanDensity				numeric(10,2),
	BestCount				int,
	ActualCount				int,
	LogicalFragmentation	numeric(10,2),
	ExtentFragmentation	numeric(10,2)
)                            
go
INSERT #ShowContigOutput
	EXEC ('DBCC SHOWCONTIG WITH TABLERESULTS')
go
SELECT ScanDensity FROM #ShowContigOutput 
	WHERE ObjectName = 'member' 
	AND IndexName = 'member_ident'
go

-- Another option would be to create a procedure and tweak it to what you want
-- for a more exact and/or flexible choice for gathering densities.

CREATE PROCEDURE sp_DBCCSHOWCONTIG
AS
DBCC SHOWCONTIG WITH TABLERESULTS
go

INSERT #ShowContigOutput
	EXEC sp_DBCCSHOWCONTIG
go
