USP_MKTCOMMON_REBUILDINDEX

Rebuilds the specified index. If no index is specified, it rebuilds all indexes on the specified table.

Parameters

Parameter	Parameter Type	Mode
@TABLENAME	nvarchar(128)	IN
@INDEXNAME	nvarchar(128)	IN
@FILLFACTOR	tinyint	IN
@SORTINTEMPDB	bit	IN

Definition

Copy



CREATE procedure dbo.[USP_MKTCOMMON_REBUILDINDEX]
(
  @TABLENAME nvarchar(128),
  @INDEXNAME nvarchar(128) = null,
  @FILLFACTOR tinyint = 100,
  @SORTINTEMPDB bit = 1
)
as
  set nocount on;

  declare @SQL nvarchar(max);

  begin try
    if @FILLFACTOR is null
      set @FILLFACTOR = 100;
    else
      begin
        if @FILLFACTOR > 100
          set @FILLFACTOR = 100;
      end

    if @SORTINTEMPDB is null
      set @SORTINTEMPDB = 1;

    --Rebuild the index on the table to clean up fragmentation...

    set @SQL = 'alter index ' + (case when len(isnull(@INDEXNAME, '')) > 0 then '[' + @INDEXNAME + ']' else 'ALL' end) + char(13) +
               'on dbo.[' + @TABLENAME + ']' + char(13) +
               'rebuild with (FILLFACTOR = ' + cast(@FILLFACTOR as nvarchar(3)) + ', SORT_IN_TEMPDB = ' + (case when @SORTINTEMPDB = 0 then 'off' else 'on' end) + ')';
    exec (@SQL);
  end try

  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;