set nocount on
----------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE name = ''CH_Index'' AND OBJECTPROPERTY(OBJECT_ID(''CH_Index''), ''IsTable'') = 1)
 BEGIN
   DROP TABLE CH_Index
 END

create table CH_Index
(
 TableName varchar(75)
,IndexName varchar(75)
,IndexType varchar(40)
,[FillFactor] int
,#ofRows int
)
go

--truncate table CH_Index
INSERT INTO CH_Index
SELECT OBJECT_NAME(si.id) ''TableName''
, si.[name] ''IndexName''
,CASE
	WHEN si.indid = 1 THEN ''CLUSTERED''
	WHEN si.indid BETWEEN 1 AND 255 THEN ''NON-CLUSTERED''
END ''IndexType''
, si.OrigFillFactor ''FillFactor''
, si.[rows] ''#ofRows''
    FROM sysindexes AS si INNER JOIN sysobjects AS so
    ON si.[id] = so.[id]
    WHERE type = ''U'' 
    AND so.[name] NOT IN (''CH_Frag'', ''CH_Index'', ''dt_properties'')
    AND si.indid  between 1 and 255
ORDER BY OBJECT_NAME(si.id) ASC, QUOTENAME(si.rows) DESC
GO


-- TRUNCATE TABLE CH_Index
-- SELECT * FROM CH_Index
------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sysobjects WHERE name = ''CH_Index'' AND OBJECTPROPERTY(OBJECT_ID(''CH_Frag''), ''IsTable'') = 1)
 BEGIN
   DROP TABLE CH_Frag
 END

CREATE TABLE CH_Frag 
(
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL
)

GO

DECLARE  @TABLENM VARCHAR(100)
DECLARE @INDEXNM varchar(100)
DECLARE @TblID int
DECLARE @IndID int
DECLARE  @SQL VARCHAR(2000)

DECLARE tmpCH_Frag CURSOR FOR
    SELECT DISTINCT OBJECT_NAME(idx.[id]), idx.[name]--, idx.indid
    FROM sysindexes AS idx INNER JOIN sysobjects AS obj
    ON obj.[id] = idx.[id]
    WHERE type = ''U'' 
    AND obj.[name] NOT IN (''CH_Frag'', ''CH_Index'', ''dt_properties'')
    AND indid  between 1 and 255

OPEN tmpCH_Frag
FETCH NEXT FROM tmpCH_Frag INTO @TABLENM, @INDEXNM

WHILE @@FETCH_STATUS = 0
BEGIN
	IF LEFT(@INDEXNM, 4) = ''_WA_''
	  BEGIN
		INSERT CH_Frag (ObjectName,ObjectId,IndexName,IndexId,Lvl,CountPages,CountRows,
				MinRecSize,MaxRecSize,AvgRecSize,ForRecCount,Extents,ExtentSwitches,
				AvgFreeBytes,AvgPageDensity,ScanDensity,BestCount,ActualCount,LogicalFrag,ExtentFrag)
		VALUES (@TABLENM, OBJECT_ID(@TABLENM), @INDEXNM, 0, 0, 0, 0, 0,0,0,0,0,0,0,0,0,0,0,0,0)
   	  END 
	ELSE
	  BEGIN
		INSERT INTO CH_Frag 
		EXEC(''DBCC SHOWCONTIG ('''''' + @TABLENM + '''''', ''''''+ @INDEXNM +'''''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'')
	  END
   
	FETCH NEXT FROM tmpCH_Frag INTO @TABLENM, @INDEXNM
END

CLOSE tmpCH_Frag
DEALLOCATE tmpCH_Frag
GO

DELETE FROM CH_Frag
WHERE IndexName IS NULL OR IndexName = ''''
GO

-- TRUNCATE TABLE CH_Frag
--select distinct * from CH_Frag order by ObjectName

--------------------------------------------------------------

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = ''RPT_INDEXEVAL'' AND xtype = ''P'' AND OBJECTPROPERTY(OBJECT_ID(''RPT_INDEXEVAL''), ''IsProcedure'') = 1)
  BEGIN
	DROP PROCEDURE RPT_INDEXEVAL
  END
GO

CREATE PROCEDURE RPT_INDEXEVAL
(
 @LessThanScanDensity 		bit	= NULL
,@ScanDensityValue		int	= NULL
,@GreaterThanLogicalScan	bit	= NULL
,@LogicalScanValue		int	= NULL
,@GreaterThanExtentScan		bit	= NULL
,@ExtentScanValue		int	= NULL
)
AS

SET NOCOUNT ON

DECLARE @LTScanDensity VARCHAR(1)
DECLARE @GTLogicalScan VARCHAR(1)
DECLARE @GTExtentScan VARCHAR(1)
DECLARE @SQLSELECT VARCHAR(2500)
DECLARE @SQLWHERE  VARCHAR(1000)
DECLARE @SQLWHERE1 VARCHAR(250)
DECLARE @SQLWHERE2 VARCHAR(250)
DECLARE @SQLWHERE3 VARCHAR(250)
DECLARE @SQLQUERY VARCHAR(1000)

SET @SQLSELECT = ''''
SET @SQLWHERE = ''''
SET @SQLQUERY = ''''

SET @LTScanDensity = (SELECT CASE @LessThanScanDensity
				WHEN 1 THEN ''<''
				ELSE NULL
			     END)

SET @GTLogicalScan = (SELECT CASE @GreaterThanLogicalScan
				WHEN 1 THEN ''>''
				ELSE NULL
			     END)

SET @GTExtentScan = (SELECT CASE @GreaterThanExtentScan
				WHEN 1 THEN ''>''
				ELSE NULL
			     END)

--------------------------------------------------------
-- INSERT SELECTED DATA INTO THE TEMP TABLE FOR EVAL  --
--------------------------------------------------------
		SELECT DISTINCT 
			  UPPER(I.TableName) ''TableName''
			, UPPER(I.IndexName) ''IndexName''
			, ISNULL(F.ScanDensity, 0) ''ScanDensity''
 			, ISNULL(F.LogicalFrag, 0) ''LogicalFrag''
			, ISNULL(F.ExtentFrag, 0) ''ExtentFrag''
			, I.IndexType ''IndexType''
 			, ISNULL(I.[FillFactor], 0)''FillFactor''
 			, ISNULL(I.#ofRows,0) ''#ofRows''
		INTO #INDEXRPT
		FROM CH_Index I
			LEFT OUTER JOIN CH_Frag F
				ON  I.IndexName = F.IndexName
		WHERE F.ObjectName NOT IN (''CH_Frag'', ''CH_Index'', ''DTPROPERTIES'')
		ORDER BY 3 ASC, 4 DESC, 5 DESC, 1, 2, 6, 7, 8

------------------------------
-- BEGIN INDEX EVALUATIONS  --
------------------------------
	SET @SQLSELECT =''SELECT IP.* ''+ CHAR(13) +
			''FROM #INDEXRPT IP ''

	IF 	(@ScanDensityValue IS NOT NULL OR
		@LogicalScanValue IS NOT NULL OR
		@ExtentScanValue IS NOT NULL)
	   BEGIN
		IF @ScanDensityValue IS NOT NULL
		  BEGIN
			SET @SQLWHERE1 = '' IP.ScanDensity '' + ISNULL(@LTScanDensity, ''='') + SPACE(1) + CAST(@ScanDensityValue AS VARCHAR(5))
			IF (CHARINDEX(''WHERE'', @SQLWHERE, 1) = 0)
			  BEGIN
				SET @SQLWHERE = CHAR(13) + ''WHERE '' + @SQLWHERE1
			  END
 			IF ((CHARINDEX(''WHERE'', @SQLWHERE, 1) > 1) and (CHARINDEX(''ScanDensity'', @SQLWHERE, 1) = 0))
			  BEGIN
				SET @SQLWHERE = @SQLWHERE + CHAR(13) + ''AND '' + @SQLWHERE1
			  END
		   END

		IF @LogicalScanValue IS NOT NULL
		  BEGIN
			SET @SQLWHERE2 = '' IP.LogicalFrag '' + ISNULL(@GTLogicalScan, ''='') + SPACE(1) + CAST(@LogicalScanValue AS VARCHAR(5))
			IF (CHARINDEX(''WHERE'', @SQLWHERE, 1) = 0)
			  BEGIN
				SET @SQLWHERE = @SQLWHERE + CHAR(13) + ''WHERE '' + @SQLWHERE2
			  END
 			IF ((CHARINDEX(''WHERE'', @SQLWHERE, 1) > 1) and (CHARINDEX(''LogicalScan'', @SQLWHERE, 1) = 0))
			  BEGIN
				SET @SQLWHERE = @SQLWHERE + CHAR(13) + ''AND '' + @SQLWHERE2
			  END
		   END

		IF @ExtentScanValue IS NOT NULL
		  BEGIN
			SET @SQLWHERE3 = '' IP.ExtentFrag '' + ISNULL(@GTExtentScan, ''='') + SPACE(1) + CAST(@ExtentScanValue AS VARCHAR(5))
			IF (CHARINDEX(''WHERE'', @SQLWHERE, 1) = 0)
			  BEGIN
				SET @SQLWHERE = @SQLWHERE + CHAR(13) + ''WHERE '' + @SQLWHERE3
			  END
 			IF ((CHARINDEX(''WHERE'', @SQLWHERE, 1) > 1) and (CHARINDEX(''ExtentScan'', @SQLWHERE, 1) = 0))
			  BEGIN
				SET @SQLWHERE = @SQLWHERE + CHAR(13) + ''AND '' + @SQLWHERE3
			  END
		   END

		SET @SQLQUERY = @SQLSELECT + @SQLWHERE+CHAR(13)+''ORDER BY 3 ASC, 4 DESC, 5 DESC, 1, 2, 6, 7, 8''
	   END
	ELSE
	   BEGIN
		SET @SQLQUERY = @SQLSELECT+CHAR(13)+''ORDER BY 3 ASC, 4 DESC, 5 DESC, 1, 2, 6, 7, 8''
	   END

--SELECT @SQLQUERY
EXEC(@SQLQUERY)

GO