Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Modify Fill Factor of Primary Keys in all tables in MS SQL 2008 R2

Is there any working method for modifying the Fill Factor arguments of all tables' Primary Keys in a Database?

like image 989
Babu James Avatar asked Jan 16 '23 08:01

Babu James


1 Answers

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'
like image 163
explunit Avatar answered Jan 24 '23 21:01

explunit