Parameters:
@report_type varchar(20) = 'none'
If and how a SELECT will be executed to report fragmentation level for each heap. Values allowed are: value | meaning |
'none' | no report |
'all' | all heaps |
'fragmented_only' | only fragmented heaps |
@print_sql_commands tinyint = 1
Whether to print the ALTER TABLE commands
@exec_sql_commands tinyint = 0
Whether to execute the ALTER TABLE commands (0 basically means "report only")
@smallest_table_size_mb int = 10
Do not rebuild if table is smaller than this value (Mb)
@largest_table_size_mb bigint = 10000
Do not rebuild if table is bigger than this (Mb)
@fragmentation_level int = 15
Rebuild if fragmentation in percent is higher than this value
@free_space_level int = 30
Rebuild if free space is higher than this valueUsage example
EXEC rebuild_heaps
@report_type = 'fragmented_only'
,@print_sql_commands = 0
,@exec_sql_commands = 1
,@smallest_table_size_mb = 10
,@largest_table_size_mb = 10000
,@fragmentation_level = 10
,@free_space_level = 30
Reference: http://www.karaszi.com/sqlserver/util_rebuild_heaps.asp
The code:You find the code here (http://www.karaszi.com/sqlserver/code/rebuild_heaps.sql.txt)