Is there any working method for modifying the Fill Factor arguments of all tables' Primary Keys in a Database?
This script finds all the primary keys and for each one builds an ALTER INDEX statement and executes it.
---- change this to your desired value ----
DECLARE @NewFillFactor smallint = 92
-------------------------------------------
DECLARE @TableName varchar(300), @IndexName varchar(300), @sql varchar(max)
DECLARE inds CURSOR FAST_FORWARD FOR
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE='PRIMARY KEY'
OPEN inds
FETCH NEXT FROM inds INTO @TableName, @IndexName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX [' + @IndexName + '] ON [dbo].[' + @TableName + '] REBUILD WITH ( FILLFACTOR = ' + cast(@NewFillFactor as varchar(3)) + ')'
PRINT @sql
EXEC(@sql)
FETCH NEXT FROM inds INTO @TableName, @IndexName
END
CLOSE inds
DEALLOCATE inds
PRINT 'Done'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With