Reset Fill Factors

Fill factor policy in one script: this walks every clustered and nonclustered index on user tables and rebuilds any whose fill factor does not match your policy - 90% for normal indexes, 70% for any index containing a uniqueidentifier column (GUIDs fragment badly at high fill). The header comment explains the reasoning behind the tiers.

/*
-- Low Update Tables (100-1 read to write ratio): 100% fill factor
-- High Update Tables (where writes exceed reads): 50%-70% fill factor
-- Everything In-Between: 80%-90% fill factor.
*/

DECLARE @Fillfactor		Int SET @Fillfactor     = 90
DECLARE @GUIDFillfactor	Int SET @GUIDFillfactor = 70
DECLARE @TableName		varchar(255)
DECLARE @IndexName		varchar(255)
DECLARE @HasGUID		bit
DECLARE @tSQL			varchar(2000)

DECLARE TableCursor CURSOR FOR
	SELECT
		'[' + schema_name(o.schema_id) + '].[' + o.name + ']',
		i.name,
		CASE WHEN IsNull(UIDs.index_id, 0) > 0 THEN 1 ELSE 0 END AS HasGUID
	FROM
		sys.indexes AS i
	INNER JOIN sys.objects as o
		ON o.object_id = i.object_id
	LEFT OUTER JOIN (
		SELECT DISTINCT
			ic.object_id,
			ic.index_id
		FROM
			sys.index_columns AS ic
		INNER JOIN sys.columns AS c
			ON c.object_ID = ic.object_id
			AND c.column_id = ic.column_id
		WHERE
			type_name(c.system_type_id) = 'uniqueidentifier'
	) AS UIDs
		ON UIDs.object_id = i.object_id
		AND UIDs.index_id = i.index_id
	WHERE
		o.type = 'U'
		AND o.is_ms_shipped = 0
		AND i.type_desc IN ('CLUSTERED', 'NONCLUSTERED')
		AND
		(
			(
				IsNull(UIDs.index_id, 0) = 0
				AND i.fill_factor <> @Fillfactor
			) OR
			(
				IsNull(UIDs.index_id, 0) > 0
				AND i.fill_factor <> @GUIDFillfactor
			)
		)
	ORDER BY
		o.name,
		CASE WHEN i.type_desc = 'CLUSTERED' THEN 1 ELSE 0 END ASC
OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName, @IndexName, @HasGUID
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT 'Setting ' +  @TableName + '.[' + @IndexName  + '] to ' + 
		Cast(CASE WHEN @HasGUID = 0 THEN @Fillfactor ELSE @GUIDFillfactor END AS VarChar(50)) + '%'

	SET @tSQL = 'ALTER INDEX [' + @IndexName + '] ON ' + @TableName
		+ ' REBUILD WITH (FILLFACTOR = '
		+ Cast(CASE WHEN @HasGUID = 0 THEN @Fillfactor ELSE @GUIDFillfactor END AS VarChar(50))
		+ ', STATISTICS_NORECOMPUTE = ON);'
	--PRINT @tSQL
	EXEC(@tSQL)

	SET @tSQL = 'DBCC DBREINDEX(''' + @TableName + ''', ''' + @IndexName + ''', '
		+ Cast(CASE WHEN @HasGUID = 0 THEN @Fillfactor ELSE @GUIDFillfactor END AS VarChar(50)) + ')'
	--PRINT @tSQL
	--EXEC @tSQL

	FETCH NEXT FROM TableCursor INTO @TableName, @IndexName, @HasGUID
END

CLOSE TableCursor DEALLOCATE TableCursor