Yay - It works! Rebuild/Reorganize all indexes in one go - woo hoo

/*Version 1 of my script to fix all fragged indexes on a database */

/*Must be logged in as [sa] for this to work, if not, you will get permissions errors*/

/*You should be able to copy and paste this script in its entirity to your SQL 2005 or higher query window and run it without altering anything other than the USE .*/

/*Perform a 'USE ' to select the database in which to run the script.*/

USE AdventureWorks

GO

-- Make sure temp table doesn't exist

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmp_fraglist]') AND type in (N'U'))

DROP TABLE [dbo].[tmp_fraglist]

GO

-- Declare variables

SET NOCOUNT ON

DECLARE @tablename NVARCHAR (255)

DECLARE @indexname NVARCHAR (255)

DECLARE @indextype NVARCHAR (255)

DECLARE @execstr NVARCHAR (255)

DECLARE @pagecounts INT

DECLARE @frag DECIMAL

DECLARE @reorgFragSize DECIMAL

DECLARE @rebldFragSize DECIMAL

DECLARE @numrecords INT

DECLARE @rowcount INT

DECLARE @errorMessage NVARCHAR (max)

-- Set the fragmentation variables for reorganize and rebuild

SELECT @reorgFragSize = 5.0

SELECT @rebldFragSize = 30.0

-- Create the temp table to store the fragged indexes

CREATE TABLE tmp_fraglist (

RowID INT IDENTITY(1,1),

ObjectName NVARCHAR (255),

IndexName NVARCHAR (255),

IndexType NVARCHAR (128),

PageCounts INT,

AvgPageFragmentation DECIMAL

)

-- Get a snapshot of current defrag state of affairs before we begin

BEGIN

SELECT '['+SCHEMA_NAME(TB.schema_id)+'].['+ TB.name +']' AS TableName,

SI.NAME AS IndexName,

INDEX_TYPE_DESC AS IndexType,

AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation,

PAGE_COUNT AS PageCounts

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') DPS

INNER JOIN sys.indexes SI

ON DPS.OBJECT_ID = SI.OBJECT_ID AND DPS.INDEX_ID = SI.index_id

INNER JOIN sys.tables TB

ON DPS.object_id = TB.object_id

WHERE index_type_desc <> 'HEAP'

AND avg_fragmentation_in_percent >= @reorgFragSize

ORDER BY TableName

END

-- Populate the temp table with table fragmentation info

INSERT INTO tmp_fraglist (ObjectName, IndexName, IndexType, AvgPageFragmentation, PageCounts)

SELECT '['+SCHEMA_NAME(TB.schema_id)+'].['+ TB.name +']' AS TableName,

SI.NAME AS IndexName,

INDEX_TYPE_DESC AS IndexType,

AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation,

PAGE_COUNT AS PageCounts

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') DPS

INNER JOIN sys.indexes SI

ON DPS.OBJECT_ID = SI.OBJECT_ID AND DPS.INDEX_ID = SI.index_id

INNER JOIN sys.tables TB

ON DPS.object_id = TB.object_id

WHERE index_type_desc <> 'HEAP'

AND avg_fragmentation_in_percent >= @reorgFragSize

SET @numrecords = @@ROWCOUNT

SET @rowcount = 1

PRINT '@numrecords = ' + convert(nvarchar,@numrecords)

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

-- loop through the records in the temp table

-- and reorganize or rebuild where necessary

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

WHILE @rowcount <= @numrecords

BEGIN

SELECT @tablename = ObjectName,

@indexname = IndexName,

@indextype = IndexType,

@frag = AvgPageFragmentation,

@pagecounts = PageCounts

FROM tmp_fraglist

WHERE RowID = @rowcount

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

-- Reorganize if fragmentation is between 5 & 30%

-- Rebuild if fragmentation is greater than 30%

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

BEGIN TRY

IF @frag >= @reorgFragSize AND @frag <= @rebldFragSize

BEGIN

SELECT @execstr = 'ALTER INDEX [' + RTRIM(@indexname) + '] ON ' + RTRIM(@tablename) + ' REORGANIZE'

PRINT @execstr

EXEC (@execstr)

PRINT 'REORGANIZED index ' + @indexname

END

ELSE IF @frag > @rebldFragSize

BEGIN

SELECT @execstr = 'ALTER INDEX [' + RTRIM(@indexname) + '] ON ' + RTRIM(@tablename) + ' REBUILD WITH (ONLINE=ON)'

PRINT @execstr

EXEC (@execstr)

PRINT 'REBUILT index ' + @indexname

END

END TRY

BEGIN CATCH

SET @errorMessage = (SELECT ERROR_MESSAGE() AS ErrorMessage)

PRINT 'Error number: ' + convert(nvarchar,@@ERROR)+ ' : ' + @errorMessage

END CATCH

SET @rowcount = @rowcount + 1

END

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

-- snapshot of the defrag status after rebuilding.

-- gets all indexes regardless of AvgPageFragmentation

-- as they may be below 5% after rebuild/reorganize

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

BEGIN

SELECT '['+SCHEMA_NAME(TB.schema_id)+'].['+ TB.name +']' AS TableName,

SI.NAME AS IndexName,

INDEX_TYPE_DESC AS IndexType,

AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation,

PAGE_COUNT AS PageCounts

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') DPS

INNER JOIN sys.indexes SI

ON DPS.OBJECT_ID = SI.OBJECT_ID AND DPS.INDEX_ID = SI.index_id

INNER JOIN sys.tables TB

ON DPS.object_id = TB.object_id

WHERE index_type_desc <> 'HEAP'

ORDER BY TableName

END

-- Delete the temporary table

DROP TABLE tmp_fraglist

GO