Skip to content

Instantly share code, notes, and snippets.

@FlogDonkey
Created June 4, 2019 22:57
Show Gist options
  • Save FlogDonkey/0a6eecabef23b1cab70f7fcb2e02b2c2 to your computer and use it in GitHub Desktop.
Save FlogDonkey/0a6eecabef23b1cab70f7fcb2e02b2c2 to your computer and use it in GitHub Desktop.
This snip generates and executes the T-SQL to set Fill Factor to 100 for any indexes; both CLUSTERED and NON-CLUSTERED.
DECLARE @RowID INT;
DECLARE @t TABLE
(
RowID INT IDENTITY(1, 1)
,command VARCHAR(MAX)
);
INSERT INTO @t
(
command
)
SELECT CASE
WHEN si.type_desc = 'CLUSTERED' THEN
'ALTER TABLE ' + s.name + '.[' + o.name + '] REBUILD WITH (FILLFACTOR = 100)'
ELSE 'ALTER INDEX ' + si.name + ' ON ' + s.name + '.[' + o.name + '] REBUILD WITH (FILLFACTOR = 100)'
END
FROM sys.indexes AS si
INNER JOIN sys.objects AS o ON o.object_id = si.object_id
INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE si.fill_factor < 100
AND si.fill_factor <> 0
AND si.is_disabled = 0
AND si.is_hypothetical = 0
AND o.is_ms_shipped = 0
AND o.type <> 'TF'
ORDER BY
o.name DESC;
SELECT @RowID = @@ROWCOUNT;
WHILE @RowID > 0
BEGIN
DECLARE @WorkingCommand VARCHAR(MAX);
SELECT @WorkingCommand = t.command
FROM @t AS t
WHERE t.RowID = @RowID;
PRINT CAST(@RowID AS VARCHAR) + ' ' + @WorkingCommand;
EXEC (@WorkingCommand);
SELECT @RowID = @RowID - 1;
END;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment