Back to OIM Explorer

dbo.QBM_PTableLockEscalationSet

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

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

References

Referenced By

Complete Source

SQL101 lines
1CREATE PROCEDURE QBM_PTableLockEscalationSet(2  @TablePattern varchar(64)3)4AS5BEGIN6  DECLARE @tablename varchar(30)7  DECLARE @SQLCmd nvarchar(max)8  DECLARE @DebugSwitch int = 09  DECLARE @AllNames QBM_YParameterList10  SET XACT_ABORT OFF11  BEGIN TRY12    IF @@TRANCOUNT > 013    BEGIN14      GOTO endLabel15    END16    SELECT17      @SQLCmd = '	select  t.name18	from sys.tables t19	where 20		t.lock_escalation <> 121		%xtp% and t.is_memory_optimized = 0 22		and t.type = ''U''23		and t.is_ms_shipped = 024		and t.name not in ( 25						''sysdiagrams''26						)27		and t.name like '''28      + @TablePattern + '''2930	'31    IF ISNULL(SERVERPROPERTY('IsXTPSupported'),32    0) = 133    BEGIN34      SELECT35        @SQLCmd = replace(@SQLcmd,36        '%xtp%',37        '')38    END39    ELSE40    BEGIN41      SELECT42        @SQLCmd = replace(@SQLcmd,43        '%xtp%',44        '--')45    END46    IF @DebugSwitch > 047    BEGIN48      print @SQLCmd49    END50    INSERT INTO @AllNames(Parameter1)51    EXEC sp_executesql @SQLcmd52    IF @@ROWCOUNT = 053    BEGIN54      GOTO endLabel55    END56    IF @DebugSwitch > 057    BEGIN58      SELECT Parameter159      FROM @AllNames60    END61    SELECT @tablename = '#'62    WHILE @tablename > ' '63    BEGIN64      SELECT @tablename = NULL65      SELECT TOP 1 @tablename = a.Parameter166      FROM @AllNames a67      IF @tablename IS NULL68      BEGIN69        CONTINUE70      END71      ELSE72      BEGIN73        DELETE @AllNames74        WHERE75          Parameter1 = @tablename76      END77      SELECT @SQLCmd = '78		alter table ' + @TableName + ' set (LOCK_ESCALATION = DISABLE)79	 '80      IF @DebugSwitch > 081      BEGIN82        print @sqlcmd83      END84      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,85        @LockTimeout_ms = DEFAULT,86        @MaxWaitTimeForLock_s = DEFAULT,87        @ProcIDForJournal = @@procid,88        @HandleErrorSilent = 089    END90  END TRY91  BEGIN CATCH92    EXEC QBM_PSessionErrorAdd DEFAULT93    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()94    RAISERROR(@Rethrow,95    18,96    1)97      WITH NOWAIT98  END CATCH99  endLabel:100  RETURN101END
Open raw exported source
SQL ยท Raw26 lines
1  create   procedure QBM_PTableLockEscalationSet(@TablePattern varchar(64) ) as begin declare @tablename varchar(30) declare @SQLCmd nvarchar(max2) declare @DebugSwitch int = 0 declare @AllNames QBM_YParameterList SET XACT_ABORT OFF BEGIN TRY if @@TRANCOUNT > 0 begin goto endLabel end select @SQLCmd3 = '	select  t.name4	from sys.tables t5	where 6		t.lock_escalation <> 17		%xtp% and t.is_memory_optimized = 0 8		and t.type = ''U''9		and t.is_ms_shipped = 010		and t.name not in ( 11						''sysdiagrams''12						)13		and t.name like '''14 + @TablePattern + '''1516	' if ISNULL(SERVERPROPERTY('IsXTPSupported'), 0) = 1 begin select @SQLCmd = replace(@SQLcmd, '%xtp%', '') end else begin select17 @SQLCmd = replace(@SQLcmd, '%xtp%', '--') end if @DebugSwitch > 0 begin print @SQLCmd end insert into @AllNames(Parameter1) exec sp_executesql @SQLcmd18 if @@ROWCOUNT = 0 begin goto endLabel end if @DebugSwitch > 0 begin select Parameter1 from @AllNames end select @tablename = '#' while @tablename > ' '19 begin select @tablename = null select top 1 @tablename = a.Parameter1 from @AllNames a if @tablename is null begin continue end else begin delete  @AllNames20 where Parameter1 = @tablename end select @SQLCmd = '21		alter table ' + @TableName + ' set (LOCK_ESCALATION = DISABLE)22	 ' if @DebugSwitch > 0 begin 23print @sqlcmd end exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal24 = @@procid , @HandleErrorSilent = 0 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow25() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: return end 26