dbo.QBM_PGIDBServerIndexRebuildOpt

1   create   procedure QBM_PGIDBServerIndexRebuildOpt(@TableName varchar(30) , @RebuildOptions nvarchar(max) output , @AllowPageLock bit = 1 ) as
2 begin declare @erg nvarchar(max) declare @SizeOfTableMB float declare @FreeSpaceTempDB float = 0.0 declare @AccessToFileSpaceUsage bit declare @UseUse
3 bit , @OnlineIndexing bit SET XACT_ABORT OFF BEGIN TRY select top 1 @UseUse = v.UseUse , @OnlineIndexing = v.OnlineIndexing , @AccessToFileSpaceUsage 
4= v.AccessToFileSpaceUsage from QBM_VServerEngineProperties v select top 1 @SizeOfTableMB = isnull(isnull(b.SizeMB, t.SizeMB), 0.0) from DialogTable t 
5with (readpast) left outer join DialogTable b with (readpast) on t.UID_DialogTableBase = b.UID_DialogTable where t.TableName = @TableName if @UseUse = 
61 and @AccessToFileSpaceUsage = 1 begin select top 1 @FreeSpaceTempDB = (SUM(unallocated_extent_page_count)*1.0/128.0) FROM tempdb.sys.dm_db_file_space_usage
7 end select @RebuildOptions = concat(' REBUILD with (SORT_IN_TEMPDB = ' , case when @UseUse = 0 then 'ON'  when @SizeOfTableMB * 5.0 < @FreeSpaceTempDB
8 then 'ON' else 'OFF' end , '
9							, Fillfactor = 100
10							, PAD_Index = ON
11							, ALLOW_PAGE_LOCKS = ', case @AllowPageLock when 1 then 'ON' 
12else 'OFF' end , '
13							, MAXDOP = 1
14						' , case @OnlineIndexing when 1 then ', ONLINE = ON' else '' end , '
15						)' ) END TRY BEGIN CATCH exec
16 QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel:
17 return end 
18