ÿþCREATE PROCEDURE [dbo].[sp_pythian_RebuildIndexes2000] (

	 @maxfrag float = 75.0  -- maximum level of acceptable fragmentation 

	,@databaseName varchar(355))



AS 

/*



Example:

EXEC sp_pythian_RebuildIndexes2000 @maxfrag=15.0, @databaseName='Northwind'

		

*/

IF OBJECT_ID('TEMPDB..#fraglist') IS NOT NULL

   DROP TABLE #fraglist



   

SET NOCOUNT ON

DECLARE @tablename VARCHAR (128)

DECLARE @tableschema VARCHAR (128)

DECLARE @execstr   VARCHAR (255)

DECLARE @objectid  INT

DECLARE @IndexName VARCHAR (255)

DECLARE @frag      DECIMAL



-- Create the temp tables

CREATE TABLE #tables(

	TABLE_NAME varchar(255)

	, TABLE_SCHEMA varchar(255)

)



INSERT INTO #tables

EXEC('SELECT TABLE_NAME, TABLE_SCHEMA FROM ' + @databaseName + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE''')



-- Declare cursor

DECLARE tables CURSOR FOR

   SELECT TABLE_NAME, TABLE_SCHEMA

   FROM #tables



-- Create the table

CREATE TABLE #fraglist (

   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)



-- Open the cursor

OPEN tables



-- Loop through all the tables in the database

FETCH NEXT

   FROM tables

   INTO @tablename, @tableschema



WHILE @@FETCH_STATUS = 0

BEGIN

-- Do the showcontig of all indexes of the table

   SELECT @tablename = @tableschema + '.' + @tablename

   INSERT INTO #fraglist

   EXEC ('USE ' + @databaseName + ' DBCC SHOWCONTIG (''' + @tablename + ''')

      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')

   FETCH NEXT

      FROM tables

      INTO @tablename, @tableschema

END

 

-- Close and deallocate the cursor

CLOSE tables

DEALLOCATE tables



-- Declare cursor for list of indexes to be defragged

DECLARE indexes CURSOR FOR

   SELECT I.TABLE_SCHEMA, F.ObjectName, F.ObjectId, F.IndexName, F.LogicalFrag

   FROM #fraglist F

   JOIN INFORMATION_SCHEMA.TABLES I ON I.TABLE_NAME = F.ObjectName COLLATE SQL_Latin1_General_CP1_CI_AS

   WHERE TABLE_TYPE = 'BASE TABLE'

   AND LogicalFrag >= @maxfrag

      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0



-- Open the cursor

OPEN indexes



-- loop through the indexes

FETCH NEXT

   FROM indexes

   INTO @tableschema, @tablename, @objectid, @IndexName, @frag



WHILE @@FETCH_STATUS = 0

BEGIN



--Check for fragmentation value

IF @frag<30

--Use DBCC INDEXDEFRAG

   BEGIN

   	SELECT @tablename = @tableschema + '.' + @tablename

   	PRINT 'Executing DBCC INDEXDEFRAG (' + RTRIM(@tablename) + ',' + RTRIM(@IndexName) + ') - fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%'

   	SELECT @execstr = 'USE ' + @databaseName + ' DBCC INDEXDEFRAG ([' + @tablename + '],' + RTRIM(@IndexName) + ')'

   END



IF @frag>=30

--Use DBCC DBREINDEX

   BEGIN

   	SELECT @tablename = @tableschema + '.' + @tablename

   	PRINT 'Executing DBCC DBREINDEX (' + RTRIM(@tablename) + ',' + RTRIM(@IndexName) + ') - fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%'

   	SELECT @execstr = 'USE ' + @databaseName + ' DBCC DBREINDEX ([' + @tablename + '],' + RTRIM(@IndexName) + ')'

   END





EXEC (@execstr)



FETCH NEXT  FROM indexes

      INTO @tableschema, @tablename, @objectid, @IndexName, @frag



END



-- Close and deallocate the cursor

CLOSE indexes

DEALLOCATE indexes



DROP TABLE #fraglist

GO