Back to OIM Explorer

dbo.QBM_PIndexDisable

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.375 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_PSessionErrorAdd source text reference

References

Referenced By

No direct source references extracted.

Complete Source

1CREATE PROCEDURE QBM_PIndexDisable(2  @tablepattern nvarchar(64) = '%',3  @IndexPattern nvarchar(64) = '%',4  @PrimaryKeyAlso BIT = 0,5  @UniqueOnly BIT = 06)7AS8BEGIN9  DECLARE @TableName varchar(30)10  DECLARE @indexname varchar(30)11  DECLARE @SQLCmd nvarchar(max)12  DECLARE @DebugSwitch int = 013  SET XACT_ABORT OFF14  BEGIN TRY15    IF @@TRANCOUNT > 016    BEGIN17      GOTO endlabel18    END19    SELECT @TableName = '#'20    WHILE @TableName > ' '21    BEGIN22      SELECT @TableName = NULL23      SELECT24        TOP 1 @TableName = t.name,25        @indexname = i.name26      FROM sys.tables t27      JOIN sys.indexes i28        ON t.object_id = i.object_id AND t.type_desc = 'USER_TABLE'29      JOIN DialogTable tt30        ON t.name = tt.TableName31      WHERE32        i.is_disabled = 0 AND t.name LIKE @tablepattern AND i.name LIKE @IndexPattern AND(i.is_primary_key = 0 OR @PrimaryKeyAlso = 1) AND(i.is_unique33      = 1 OR @UniqueOnly = 0) AND i.is_hypothetical = 0 AND t.is_memory_optimized = 0 AND i.type NOT IN(3,34      4,35      5,36      6)37      IF @TableName IS NULL38      BEGIN39        CONTINUE40      END41      IF @IndexPattern = '%' AND @PrimaryKeyAlso = 1 AND @UniqueOnly = 042      BEGIN43        SELECT @SQLCmd = 'ALTER INDEX ALL on ' + @TableName + ' Disable'44        IF @DebugSwitch > 045        BEGIN46          print @sqlcmd47        END48        EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @sqlcmd,49          @LockTimeout_ms = DEFAULT,50          @MaxWaitTimeForLock_s = DEFAULT,51          @ProcIDForJournal = DEFAULT,52          @HandleErrorSilent = 0,53          @ExecuteWithTransact = 054      END55      ELSE56      BEGIN57        SELECT @indexname = '#'58        WHILE @indexname > ' '59        BEGIN60          SELECT @indexname = NULL61          SELECT TOP 1 @indexname = i.name62          FROM sys.tables t63          JOIN sys.indexes i64            ON t.object_id = i.object_id AND t.type_desc = 'USER_TABLE'65          JOIN DialogTable tt66            ON t.name = tt.TableName67          WHERE68            i.is_disabled = 0 AND t.name = @TableName AND i.name LIKE @IndexPattern AND i.name > ' ' AND(i.is_primary_key = 0 OR @PrimaryKeyAlso = 169          ) AND(i.is_unique = 1 OR @UniqueOnly = 0) AND i.is_hypothetical = 0 AND i.type NOT IN(3,70          4,71          5,72          6)73          IF @indexname IS NULL74          BEGIN75            CONTINUE76          END77          SELECT @SQLCmd = 'ALTER INDEX "' + @indexname + '" on ' + @TableName + ' Disable'78          IF @DebugSwitch > 079          BEGIN80            print @sqlcmd81          END82          EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @sqlcmd,83            @LockTimeout_ms = DEFAULT,84            @MaxWaitTimeForLock_s = DEFAULT,85            @ProcIDForJournal = DEFAULT,86            @HandleErrorSilent = 0,87            @ExecuteWithTransact = 088        END89      END90    END91  END TRY92  BEGIN CATCH93    EXEC QBM_PSessionErrorAdd DEFAULT94    RAISERROR('',95    18,96    1)97      WITH NOWAIT98  END CATCH99  endLabel:100  RETURN101END

Open raw exported source

1  create   procedure QBM_PIndexDisable ( @tablepattern nvarchar(64) = '%' , @IndexPattern nvarchar(64) = '%' , @PrimaryKeyAlso bit = 0 , @UniqueOnly2 bit = 0 ) as begin declare @TableName varchar(30) declare @indexname varchar(30) declare @SQLCmd nvarchar(max) declare @DebugSwitch int = 0 SET XACT_ABORT3 OFF BEGIN TRY if @@TRANCOUNT > 0 begin  goto endlabel end  select @TableName = '#' while @TableName > ' ' begin select @TableName = null select top 1 4@TableName = 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 DialogTable5 tt on t.name = tt.TableName where i.is_disabled = 0 and t.name like @tablepattern and i.name like @IndexPattern and ( i.is_primary_key = 0 or @PrimaryKeyAlso6 = 1) and (i.is_unique = 1 or @UniqueOnly = 0) and i.is_hypothetical = 0 and t.is_memory_optimized = 0  and i.type not in (3,4,5,6) if @TableName is null7 begin continue end if @IndexPattern = '%' and @PrimaryKeyAlso = 1 and @UniqueOnly = 0 begin  select @SQLCmd = 'ALTER INDEX ALL on ' + @TableName + ' Disable'8 if @DebugSwitch > 0 begin print @sqlcmd end exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @sqlcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s9 = default , @ProcIDForJournal = default , @HandleErrorSilent = 0  , @ExecuteWithTransact = 0 end else begin  select @indexname = '#' while @indexname 10> ' ' begin select @indexname = null select top 1 @indexname = i.name from sys.tables t join sys.indexes i on t.object_id = i.object_id and t.type_desc11 = 'USER_TABLE'  join DialogTable tt on t.name = tt.TableName where i.is_disabled = 0 and t.name = @TableName and i.name like @IndexPattern and i.name 12> ' ' and (i.is_primary_key = 0 or @PrimaryKeyAlso = 1) and (i.is_unique = 1 or @UniqueOnly = 0) and i.is_hypothetical = 0  and i.type not in (3,4,5,6)13 if @indexname is null begin continue end select @SQLCmd = 'ALTER INDEX "' + @indexname + '" on ' + @TableName + ' Disable' if @DebugSwitch > 0 begin print14 @sqlcmd end exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @sqlcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal15 = default , @HandleErrorSilent = 0  , @ExecuteWithTransact = 0 end  end  end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18,16 1) WITH NOWAIT END CATCH endLabel: return end  17