Skip to content

Instantly share code, notes, and snippets.

@kshimi
Created March 14, 2019 09:57
Show Gist options
  • Save kshimi/99881726b83900a5ed17a5a19020b6e8 to your computer and use it in GitHub Desktop.
Save kshimi/99881726b83900a5ed17a5a19020b6e8 to your computer and use it in GitHub Desktop.
SQLServer rebuild index
DECLARE @SchemaName sysname, @TableName sysname, @IndexName sysname
DECLARE @basesql nvarchar(max), @sql nvarchar(max)
SET @basesql = 'ALTER INDEX @1 On @2 REBUILD WITH ( FILLFACTOR = 50 ) '
DECLARE IXC CURSOR FOR
SELECT
OBJECT_SCHEMA_NAME(i.object_id) As SchemaName
, OBJECT_NAME(i.object_id) AS TableName
, i.name AS IndexName
FROM
sys.indexes i
inner join sys.dm_db_index_physical_stats (DB_ID(N'FA'), NULL, NULL, NULL, NULL) s
on i.object_id = s.object_id and i.index_id = s.index_id
WHERE
OBJECT_SCHEMA_NAME (i.object_id) <> 'sys'
AND
i.index_id > 0
AND s.avg_fragmentation_in_percent > 30
AND s.fragment_count > 1000
OPEN IXC
FETCH NEXT FROM IXC
INTO @SchemaName, @TableName, @IndexName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @SchemaName + '.' + @TableName + ':' + @IndexName
SET @sql = REPLACE(@basesql, '@1', @IndexName)
SET @sql = REPLACE(@sql, '@2', @SchemaName + '.' + @TableName)
EXECUTE (@sql)
FETCH NEXT FROM IXC
INTO @SchemaName, @TableName, @IndexName
END
CLOSE IXC
DEALLOCATE IXC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment