Back to OIM Explorer

dbo.QBM_PIndexEnable

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.567 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_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PGIDBServerIndexRebuildOpt source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PSetRowLockOnly source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL104 lines
1CREATE PROCEDURE QBM_PIndexEnable(2  @tablepattern nvarchar(64) = '%',3  @IndexPattern nvarchar(64) = '%'4)5AS6BEGIN7  DECLARE @TableName varchar(30)8  DECLARE @indexname varchar(30)9  DECLARE @SQLCmd nvarchar(max)10  DECLARE @RebuildOptions nvarchar(max)11  DECLARE @DebugSwitch int = 012  SET XACT_ABORT OFF13  BEGIN TRY14    IF @@TRANCOUNT > 015    BEGIN16      GOTO endlabel17    END18    SELECT @TableName = '#'19    WHILE @TableName > ' '20    BEGIN21      SELECT @TableName = NULL22      SELECT23        TOP 1 @TableName = t.name,24        @indexname = i.name25      FROM sys.tables t26      JOIN sys.indexes i27        ON t.object_id = i.object_id AND t.type_desc = 'USER_TABLE'28      JOIN DialogTable tt29        ON t.name = tt.TableName30      WHERE31        i.is_disabled = 1 AND t.name LIKE @tablepattern AND i.name LIKE @IndexPattern AND t.is_memory_optimized = 0 AND i.is_hypothetical = 0 AND32      i.type NOT IN(3,33      4,34      5,35      6)36      IF @TableName IS NULL37      BEGIN38        CONTINUE39      END40      EXEC QBM_PGIDBServerIndexRebuildOpt @TableName,41        @RebuildOptions OUTPUT42      SELECT @SQLCmd = 'ALTER INDEX ALL on ' + @TableName + ' SET (ALLOW_PAGE_LOCKS = ON)'43      IF @DebugSwitch > 0 print @SQLCmd44      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @sqlcmd,45        @LockTimeout_ms = DEFAULT,46        @MaxWaitTimeForLock_s = DEFAULT,47        @ProcIDForJournal = @@procid,48        @HandleErrorSilent = 0,49        @ExecuteWithTransact = 050      IF @IndexPattern = '%'51      BEGIN52        SELECT @SQLCmd = 'ALTER INDEX ALL on ' + @TableName + @RebuildOptions53        IF @DebugSwitch > 0 print @SQLCmd54        EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @sqlcmd,55          @LockTimeout_ms = DEFAULT,56          @MaxWaitTimeForLock_s = DEFAULT,57          @ProcIDForJournal = @@procid,58          @HandleErrorSilent = 0,59          @ExecuteWithTransact = 060      END61      ELSE62      BEGIN63        SELECT @indexname = '#'64        WHILE @indexname > ' '65        BEGIN66          SELECT @indexname = NULL67          SELECT TOP 1 @indexname = i.name68          FROM sys.tables t69          JOIN sys.indexes i70            ON t.object_id = i.object_id AND t.type_desc = 'USER_TABLE'71          JOIN DialogTable tt72            ON t.name = tt.TableName73          WHERE74            i.is_disabled = 1 AND t.name = @TableName AND i.name LIKE @IndexPattern AND i.name > ' ' AND i.is_hypothetical = 0 AND i.type NOT IN(3,75          4,76          5,77          6)78          IF @indexname IS NULL79          BEGIN80            CONTINUE81          END82          SELECT @SQLCmd = 'ALTER INDEX "' + @indexname + '" on ' + @TableName + @RebuildOptions83          IF @DebugSwitch > 0 print @SQLCmd84          EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @sqlcmd,85            @LockTimeout_ms = DEFAULT,86            @MaxWaitTimeForLock_s = DEFAULT,87            @ProcIDForJournal = @@procid,88            @HandleErrorSilent = 0,89            @ExecuteWithTransact = 090        END91      END92      EXEC QBM_PSetRowLockOnly @TableName93    END94  END TRY95  BEGIN CATCH96    EXEC QBM_PSessionErrorAdd DEFAULT97    RAISERROR('',98    18,99    1)100      WITH NOWAIT101  END CATCH102  endLabel:103  RETURN104END
Open raw exported source
SQL ยท Raw18 lines
1     create   procedure QBM_PIndexEnable ( @tablepattern nvarchar(64) = '%' , @IndexPattern nvarchar(64) = '%' ) as begin declare @TableName varchar2(30) declare @indexname varchar(30) declare @SQLCmd nvarchar(max) declare @RebuildOptions nvarchar(max) declare @DebugSwitch int = 0 SET XACT_ABORT OFF3 BEGIN TRY if @@TRANCOUNT > 0 begin  goto endlabel end  select @TableName = '#' while @TableName > ' ' begin select @TableName = null select top 1 @TableName4 = t.name , @indexname = i.name from sys.tables t join sys.indexes i on t.object_id = i.object_id and t.type_desc = 'USER_TABLE'  join DialogTable tt on5 t.name = tt.TableName where i.is_disabled = 1 and t.name like @tablepattern and i.name like @IndexPattern and t.is_memory_optimized = 0  and i.is_hypothetical6 = 0  and i.type not in (3,4,5,6) if @TableName is null begin continue end exec QBM_PGIDBServerIndexRebuildOpt @TableName, @RebuildOptions output  select7 @SQLCmd = 'ALTER INDEX ALL on ' + @TableName + ' SET (ALLOW_PAGE_LOCKS = ON)' if @DebugSwitch > 0 print @SQLCmd exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement8 = @sqlcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0  , @ExecuteWithTransact9 = 0 if @IndexPattern = '%' begin  select @SQLCmd = 'ALTER INDEX ALL on ' + @TableName + @RebuildOptions if @DebugSwitch > 0 print @SQLCmd exec QBM_PExecuteSQLWithRetry_LLP10 @SQLStatement = @sqlcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0  , @ExecuteWithTransact11 = 0 end else begin  select @indexname = '#' while @indexname > ' ' begin select @indexname = null select top 1 @indexname = i.name from sys.tables t join12 sys.indexes i on t.object_id = i.object_id and t.type_desc = 'USER_TABLE'  join DialogTable tt on t.name = tt.TableName where i.is_disabled = 1 and t.name13 = @TableName and i.name like @IndexPattern and i.name > ' '  and i.is_hypothetical = 0  and i.type not in (3,4,5,6) if @indexname is null begin continue14 end select @SQLCmd = 'ALTER INDEX "' + @indexname + '" on ' + @TableName + @RebuildOptions if @DebugSwitch > 0 print @SQLCmd exec QBM_PExecuteSQLWithRetry_LLP15 @SQLStatement = @sqlcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0  , @ExecuteWithTransact16 = 0 end  end  exec QBM_PSetRowLockOnly @TableName end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH17 endLabel: return end  18