Hi,

Very nice script. I've been searching ages for this. I am not so comfortable with cursors, they tend to always crash for me.

However,

Do you really want to reorganize or rebuild indexes that has no pages?

I my self only look for defragmentation when page_count are above 1000 for small databases and 500 for bigger ones.

Your script is easily changed, as it is a very good script, to perform this.

I have done this

Added a variable ,@PctCount INT in DECLARE

Added av Column and a Value in #Frag table (pCount INT having pCount = s.page_count + Added AND s.page_count > 500 in Where clause)

Added ,@PctCount to the CURSOR AND FETCH

Inserted IF @PctCount > 499 WITH a BEGIN and END

-- CHOOSE DB TO CHECK FIRST (or master will be checked as default)

SET NOCOUNT ON

DECLARE @DBName NVARCHAR(255)

,@TableName NVARCHAR(255)

,@SchemaName NVARCHAR(255)

,@IndexName NVARCHAR(255)

,@PctFrag DECIMAL

,@PctCount INT

DECLARE @Defrag NVARCHAR(MAX)

CREATE TABLE #Frag

(DBName NVARCHAR(255)

,TableName NVARCHAR(255)

,SchemaName NVARCHAR(255)

,IndexName NVARCHAR(255)

,AvgFragment DECIMAL

,pCount INT)

EXEC sp_msforeachdb 'INSERT INTO #Frag (

DBName,

TableName,

SchemaName,

IndexName,

AvgFragment,

pCount

) SELECT ''?'' AS DBName

,t.Name AS TableName

,sc.Name AS SchemaName

,i.name AS IndexName

,s.avg_fragmentation_in_percent

,s.page_count

--,s.*

FROM ?.sys.dm_db_index_physical_stats(DB_ID(''?''), NULL, NULL,

NULL, ''Sampled'') AS s

JOIN ?.sys.indexes i

ON s.Object_Id = i.Object_id

AND s.Index_id = i.Index_id

JOIN ?.sys.tables t

ON i.Object_id = t.Object_Id

JOIN ?.sys.schemas sc

ON t.schema_id = sc.SCHEMA_ID

WHERE s.avg_fragmentation_in_percent > 10

AND s.page_count > 500

AND t.TYPE = ''U''

ORDER BY TableName,IndexName'

SELECT * FROM #Frag

DECLARE cList CURSOR

FOR SELECT * FROM #Frag

OPEN cList

FETCH NEXT FROM cList

INTO @DBName, @TableName,@SchemaName,@IndexName,@PctFrag,@PctCount

WHILE @@FETCH_STATUS = 0

BEGIN

IF @PctCount > 499

BEGIN

IF @PctFrag BETWEEN 10.0 AND 30.0

BEGIN

SET @Defrag = N'ALTER INDEX [' + @IndexName + '] ON [' + @DBName + '].[' + @SchemaName + '].[' + @TableName + '] REORGANIZE'

EXEC sp_executesql @Defrag

PRINT @Defrag

PRINT ''

END

ELSE IF @PctFrag > 30.0

BEGIN

SET @Defrag = N'ALTER INDEX [' + @IndexName + '] ON [' + @DBName + '].[' + @SchemaName + '].[' + @TableName + '] REBUILD'

EXEC sp_executesql @Defrag

PRINT @Defrag

PRINT ''

END

END

FETCH NEXT FROM cList

INTO @DBName, @TableName,@SchemaName,@IndexName,@PctFrag,@PctCount

END

CLOSE cList

DEALLOCATE cList

DROP TABLE #Frag