Hi,
I found this sometime ago. I apologize to the author that I do not remember him or where I got it from.
I have made som changes to it to get the fillfactor at the same time. But it really works and it uses ms_foreachdb so all databases are covered.
Considerations must be taken on allow_row_locks and allow page_locks.
If the are not allowed than exclude those databases in the script and change the rebuild syntax in the code
----------- CODE -------------
SET NOCOUNT ON
DECLARE @DBName NVARCHAR(255)
,@TableName NVARCHAR(255)
,@SchemaName NVARCHAR(255)
,@IndexName NVARCHAR(255)
,@PctFrag DECIMAL
,@PctCount INT
,@PctFactor INT
DECLARE @Defrag NVARCHAR(MAX)
CREATE TABLE #Frag
(DBName NVARCHAR(255)
,TableName NVARCHAR(255)
,SchemaName NVARCHAR(255)
,IndexName NVARCHAR(255)
,AvgFragment FLOAT
,pCount INT
,fFactor INT)
EXEC sp_msforeachdb 'INSERT INTO #Frag (
DBName,
TableName,
SchemaName,
IndexName,
AvgFragment,
pCount,
fFactor
) SELECT ''?'' AS ''DBName''
,t.Name AS ''TableName''
,sc.Name AS ''SchemaName''
,i.name AS ''IndexName''
,s.avg_fragmentation_in_percent
,s.page_count
,i.fill_factor
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 >= 5
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,@PctFactor
WHILE @@FETCH_STATUS = 0
BEGIN
IF @PctCount > 499
BEGIN
IF @PctFrag BETWEEN 5.0 AND 30.0
BEGIN
SET @Defrag = N'ALTER INDEX [' + @IndexName + '] ON [' + @DBName + '].[' + @SchemaName + '].[' + @TableName + '] REORGANIZE WITH ( LOB_COMPACTION = ON )'
EXEC sp_executesql @Defrag
PRINT @Defrag
PRINT ''
END
ELSE IF @PctFrag > 30.0
BEGIN
IF @PctFactor = 0
BEGIN
SET @Defrag = N'ALTER INDEX [' + @IndexName + '] ON [' + @DBName + '].[' + @SchemaName + '].[' + @TableName + '] REBUILD WITH (FILLFACTOR = 90, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)'
-- change above REBUILD to what you need
-- these settings will set fillfactor to 90 and allow for row and page locks
END
ELSE
BEGIN
SET @Defrag = N'ALTER INDEX [' + @IndexName + '] ON [' + @DBName + '].[' + @SchemaName + '].[' + @TableName + '] REBUILD WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)'
END
EXEC sp_executesql @Defrag
PRINT @Defrag
PRINT ''
END
END
FETCH NEXT FROM cList
INTO @DBName, @TableName,@SchemaName,@IndexName,@PctFrag,@PctCount,@PctFactor
END
CLOSE cList
DEALLOCATE cList
DROP TABLE #Frag