Back to OIM Explorer

dbo.QBM_PGIDBServerIndexRebuildOpt

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 1.548 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL77 lines
1CREATE PROCEDURE QBM_PGIDBServerIndexRebuildOpt(2  @TableName varchar(30),3  @RebuildOptions nvarchar(max) OUTPUT,4  @AllowPageLock BIT = 15)6AS7BEGIN8  DECLARE @erg nvarchar(max)9  DECLARE @SizeOfTableMB float10  DECLARE @FreeSpaceTempDB float = 0.011  DECLARE @AccessToFileSpaceUsage BIT12  DECLARE @UseUse BIT,13  @OnlineIndexing BIT14  SET XACT_ABORT OFF15  BEGIN TRY16    SELECT17      TOP 1 @UseUse = v.UseUse,18      @OnlineIndexing = v.OnlineIndexing,19      @AccessToFileSpaceUsage = v.AccessToFileSpaceUsage20    FROM QBM_VServerEngineProperties v21    SELECT22      TOP 1 @SizeOfTableMB = isnull(isnull(b.SizeMB, t.SizeMB),23      0.0)24    FROM DialogTable t25      WITH(readpast)26    LEFT27    OUTER28    JOIN DialogTable b29      WITH(readpast)30      ON t.UID_DialogTableBase = b.UID_DialogTable31    WHERE32      t.TableName = @TableName33    IF @UseUse = 1 AND @AccessToFileSpaceUsage = 134    BEGIN35      SELECT TOP 1 @FreeSpaceTempDB =(SUM(unallocated_extent_page_count) *1.0/128.0)36      FROM tempdb.sys.dm_db_file_space_usage37    END38    SELECT39      @RebuildOptions = CONCAT(' REBUILD with (SORT_IN_TEMPDB = ',40    CASE41      WHEN @UseUse = 0 THEN42    'ON'43      WHEN @SizeOfTableMB * 5.0 < @FreeSpaceTempDB THEN44    'ON'45    ELSE 'OFF'46    END,47    '48							, Fillfactor = 10049							, PAD_Index = ON50							, ALLOW_PAGE_LOCKS = ',51    CASE @AllowPageLock52      WHEN 1 THEN53    'ON'54    ELSE 'OFF'55    END,56    '57							, MAXDOP = 158						',59    CASE @OnlineIndexing60      WHEN 1 THEN61    ', ONLINE = ON'62    ELSE ''63    END,64    '65						)')66  END TRY67  BEGIN CATCH68    EXEC QBM_PSessionErrorAdd DEFAULT69    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()70    RAISERROR(@Rethrow,71    18,72    1)73      WITH NOWAIT74  END CATCH75  endLabel:76  RETURN77END
Open raw exported source
SQL ยท Raw18 lines
1   create   procedure QBM_PGIDBServerIndexRebuildOpt(@TableName varchar(30) , @RebuildOptions nvarchar(max) output , @AllowPageLock bit = 1 ) as2 begin declare @erg nvarchar(max) declare @SizeOfTableMB float declare @FreeSpaceTempDB float = 0.0 declare @AccessToFileSpaceUsage bit declare @UseUse3 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_usage7 end select @RebuildOptions = concat(' REBUILD with (SORT_IN_TEMPDB = ' , case when @UseUse = 0 then 'ON'  when @SizeOfTableMB * 5.0 < @FreeSpaceTempDB8 then 'ON' else 'OFF' end , '9							, Fillfactor = 10010							, PAD_Index = ON11							, ALLOW_PAGE_LOCKS = ', case @AllowPageLock when 1 then 'ON' 12else 'OFF' end , '13							, MAXDOP = 114						' , case @OnlineIndexing when 1 then ', ONLINE = ON' else '' end , '15						)' ) END TRY BEGIN CATCH exec16 QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel:17 return end 18