DECLARE @tableName NVARCHAR(500) DECLARE @indexName NVARCHAR(500) DECLARE @indexType NVARCHAR(55) DECLARE @sql NVARCHAR(500) DECLARE @percentFragment DECIMAL(11, 2) DECLARE fragmentedtablelist CURSOR FOR SELECT '[' + Object_schema_name(ind.object_id) + '].[' + 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 > 5 AND ind.NAME IS NOT NULL ORDER BY tablename, ind.NAME OPEN fragmentedtablelist FETCH next FROM fragmentedtablelist INTO @tableName, @indexName, @indexType, @percentFragment WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'ALTER INDEX ' + @indexName + ' ON ' + @tableName + ' REBUILD; ' PRINT CONVERT(VARCHAR, Getdate(), 14) + ' - ' + @indexName + ' - ' + Cast(@percentFragment AS NVARCHAR(50)) + ' - ' + ( @sql ) EXEC (@sql) FETCH next FROM fragmentedtablelist INTO @tableName, @indexName, @indexType, @percentFragment END CLOSE fragmentedtablelist DEALLOCATE fragmentedtablelist
Wednesday, 30 January 2019
SQL Server Reindexing Query (By Index)
This query is used to reindex the SQL Server database tables index by index