Rebuild all Index

DECLARE @TableName sysname
declare @cmd varchar(250)
DECLARE cur_showfragmentation CURSOR FOR

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName
--ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
--indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30
ORDER BY indexstats.avg_fragmentation_in_percent DESC

OPEN cur_showfragmentation
FETCH NEXT FROM cur_showfragmentation INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Deframmentazione indici di ' + @TableName + ' Tabella'
set @cmd = 'ALTER INDEX ALL ON ' + @TableName +' REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON)'
EXEC (@cmd)
FETCH NEXT FROM cur_showfragmentation INTO @TableName
END
CLOSE cur_showfragmentation
DEALLOCATE cur_showfragmentation

Total Views Views Today

Nessun commento ancora

Leave a reply