because PAD_INDEX uses the percentage specified by FILLFACTOR.

The PAD_INDEX option is useful only when FILLFACTOR is specified, For more information, see (3) Specifies index padding. must be an integer value from 1 to 100 The default is 0. of each index page during index creation or alteration. Specifies a percentage that indicates how full the Database Engine should make the leaf level PRINT - uncomment if you want to see commandsįETCH NEXT FROM TableCursor INTO TableCursorįETCH NEXT FROM DatabaseCursor INTO DatabaseCursorĪlso a good script, although my laptop ran out of memory, but this was on a very large table SET = 'ALTER INDEX ALL ON ' + + ' REBUILD' INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE''' SET = 'DECLARE TableCursor CURSOR READ_ONLY FOR SELECT ''.['' + WHERE name IN ('YOUR DATABASE NAME') - databasesĪND is_in_standby = 0 - database is not read only for log shippingįETCH NEXT FROM DatabaseCursor INTO = 0 DECLARE NVARCHAR(255)ĭECLARE DatabaseCursor CURSOR READ_ONLY FOR Replace the 'YOUR DATABASE NAME' in the query below. ORDER BY indexstats.avg_fragmentation_in_percent descįor more information, Check the following link:

WHERE indexstats.database_id = DB_ID() AND dbtables. = indexstats.ĪND indexstats.index_id = dbindexes.index_id INNER JOIN sys.indexes AS dbindexes ON dbindexes. INNER JOIN sys.schemas dbschemas on dbtables. INNER JOIN sys.tables dbtables on dbtables. as 'Schema',įROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats SET = 'ALTER INDEX ALL ON ' + + ' REBUILD WITH (FILLFACTOR = ' + + ')'ĮXEC NEXT FROM TableCursor INTO TableCursorĪnd if you want to Check Index Fragmentation on Indexes in a Database, try the following script: SELECT dbschemas. SELECT QUOTENAME(OBJECT_SCHEMA_NAME())+'.' + QUOTENAME(name) AS TableName I prefer(After a long search) to use the following script, it contains determines how much percentage of the space on each leaf-level page is filled with data. Try the following script: Exec sp_msforeachtable 'SET QUOTED_IDENTIFIER ON ALTER INDEX ALL ON ? REBUILD'

Rebuild outlook identity database