#MSSQL Set Fill Factor for table

set_fill_factor.sql

DECLARE @Table varchar(255) = 'TableName'
DECLARE @Test char(1) = 'N'
DECLARE @FillFactorOverride varchar(3) = NULL
-- SET @Test = 'Y' to only output computed fill factor values. No database modifications will occur.
-- Set @FillFactorOverride to value other than NULL to override computed fill factor value
-- Example: @FillFactorOverride = '90'
-- =================================
DECLARE @CursorTable varchar(255)
DECLARE @MyIndex varchar(255)
DECLARE @MyFillFactor varchar(3)
DECLARE @MyKeySize int
DECLARE @MySql nvarchar(max)
DECLARE Index_cursor CURSOR FOR
SELECT
  '[' + s.name + '].[' + t.name + ']' AS TableName,
  '[' + i.name + ']' AS IndexName,
  SUM(c.Max_length) AS [KeySize]
FROM sys.tables t
INNER JOIN sys.schemas s
  ON t.schema_id = s.schema_id
INNER JOIN sys.indexes i
  ON i.object_id = t.object_id
INNER JOIN sys.index_columns ic
  ON ic.object_id = t.object_id
INNER JOIN sys.columns c
  ON c.object_id = t.object_id
  AND ic.column_id = c.column_id
WHERE
i.type IN (1, 2)
-- must be a key column
AND ic.key_ordinal > 0
AND t.name = @Table
GROUP BY s.name,
         t.name,
         i.name

OPEN Index_cursor
FETCH NEXT FROM Index_cursor
INTO @CursorTable, @MyIndex, @MyKeySize
WHILE @@FETCH_STATUS = 0
BEGIN
  IF @MyKeySize <= 900
    SET @MyFillFactor = '97'
  IF @MyKeySize <= 848
    SET @MyFillFactor = '96'
  IF @MyKeySize <= 403
    SET @MyFillFactor = '95'
  IF @MyKeySize <= 222
    SET @MyFillFactor = '94'
  IF @MyKeySize <= 135
    SET @MyFillFactor = '93'
  IF @MyKeySize <= 89
    SET @MyFillFactor = '92'
  IF @MyKeySize <= 61
    SET @MyFillFactor = '91'
  IF @MyKeySize <= 44
    SET @MyFillFactor = '90'
  IF @MyKeySize <= 33
    SET @MyFillFactor = '89'
  IF @MyKeySize <= 25
    SET @MyFillFactor = '88'
  IF @MyKeySize <= 20
    SET @MyFillFactor = '87'
  IF @MyKeySize <= 16
    SET @MyFillFactor = '86'
  IF @MyKeySize <= 13
    SET @MyFillFactor = '85'
  IF @MyKeySize <= 10
    SET @MyFillFactor = '84'
  IF @MyKeySize <= 9
    SET @MyFillFactor = '83'
  IF @MyKeySize <= 7
    SET @MyFillFactor = '82'
  IF @MyKeySize <= 6
    SET @MyFillFactor = '81'
  IF @MyKeySize <= 5
    SET @MyFillFactor = '79'
  IF @MyKeySize <= 4
    SET @MyFillFactor = '78'
  IF @MyKeySize <= 3
    SET @MyFillFactor = '75'
  IF @MyKeySize <= 2
    SET @MyFillFactor = '71'

  IF @FillFactorOverride IS NULL
    BEGIN
      SET @MySql = 'ALTER INDEX ' + @MyIndex + ' ON ' + @CursorTable + ' REBUILD WITH
	   (PAD_INDEX=ON, FILLFACTOR = ' + @MyFillFactor + ')'
    END
  ELSE 
    BEGIN
      SET @MySql = 'ALTER INDEX ' + @MyIndex + ' ON ' + @CursorTable + ' REBUILD WITH
	   (PAD_INDEX=ON, FILLFACTOR = ' + @FillFactorOverride + ')'
    END

  IF @Test = 'N'
	 BEGIN
	   EXEC (@MySql)
	   PRINT @MySql + ' | COMPLETE'
	 END
  ELSE IF @Test = 'Y'
	 BEGIN
	   PRINT @MySql + ' | TEST'
	 END

  FETCH NEXT FROM Index_cursor INTO @CursorTable, @MyIndex, @MyKeySize
END
CLOSE Index_cursor
DEALLOCATE Index_cursor