Back to OIM Explorer

dbo.QBM_PSetRowLockOnly

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.466 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_FGIObjectIsDropable 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

1CREATE PROCEDURE QBM_PSetRowLockOnly(2  @TablePattern nvarchar(64) = '%'3)4AS5BEGIN6  DECLARE @SQLcmd1 nvarchar(1024)7  DECLARE @SQLcmd2 nvarchar(1024)8  DECLARE @ElementBuffer QBM_YCursorBuffer9  DECLARE @ElementCount int10  DECLARE @ElementIndex int11  DECLARE @DebugSwitch int = 012  DECLARE @LogMessage nvarchar(max)13  SET XACT_ABORT OFF14  BEGIN TRY15    SET nocount16      ON17    INSERT INTO @ElementBuffer(ContentShort,18    ContentFull)19    SELECT20      DISTINCT CASE i.allow_row_locks21      WHEN 0 THEN22      CONCAT('ALTER INDEX ',23      isnull(i.name, 'ALL'),24      ' on ',25      t.name,26      ' SET (ALLOW_ROW_LOCKS = ON)')27    ELSE ''28    END,29    CASE i.allow_page_locks30      WHEN 1 THEN31    CONCAT('ALTER INDEX ',32    isnull(i.name, 'ALL'),33    ' on ',34    t.name,35    ' SET (ALLOW_PAGE_LOCKS = OFF)')36    ELSE ''37    END38    FROM sys.tables t39    JOIN sys.schemas o40      ON t.schema_id = o.schema_id AND dbo.QBM_FGIObjectIsDropable(o.schema_id) =141    JOIN sys.indexes i42      ON i.object_id = t.object_id43    WHERE44      t.type = 'U' AND t.name LIKE @TablePattern AND t.is_memory_optimized = 0 AND i.is_hypothetical = 0 AND i.type NOT IN(3,45    4,46    5,47    6) AND t.name NOT LIKE 'sys%' AND t.name NOT LIKE 'ms%' AND t.name NOT LIKE 'IH%' AND t.name NOT LIKE 'conflict%' AND(i.allow_row_locks = 0 OR48    i.allow_page_locks = 1) AND i.type_desc IN('CLUSTERED',49    'NONCLUSTERED',50    'HEAP') AND NOT(i.type_desc = 'HEAP' AND EXISTS(51    SELECT TOP 1 152    FROM sys.indexes ii53    WHERE54      i.object_id = ii.object_id AND ii.type_desc IN('CLUSTERED COLUMNSTORE', 'NONCLUSTERED COLUMNSTORE'))) AND o.name = 'dbo'55    ORDER BY 156    SELECT @ElementCount = @@ROWCOUNT57    SELECT @ElementIndex = 158    WHILE @ElementIndex <= @ElementCount59    BEGIN60      SELECT61        TOP 1 @SQLcmd1 = bu.ContentShort,62        @SQLcmd2 = bu.ContentFull63      FROM @ElementBuffer bu64      WHERE65        bu.ElementIndex = @ElementIndex66      IF @DebugSwitch > 067      BEGIN68        print @SQLcmd1 print @SQLcmd269      END70      IF @SQLcmd1 > ' '71      BEGIN72        EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd1,73          @LockTimeout_ms = DEFAULT,74          @MaxWaitTimeForLock_s = DEFAULT,75          @ProcIDForJournal = @@procid,76          @HandleErrorSilent = 0,77          @ExecuteWithTransact = 078      END79      IF @SQLcmd2 > ' '80      BEGIN81        EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd2,82          @LockTimeout_ms = DEFAULT,83          @MaxWaitTimeForLock_s = DEFAULT,84          @ProcIDForJournal = @@procid,85          @HandleErrorSilent = 0,86          @ExecuteWithTransact = 087      END88      SELECT @ElementIndex += 189    END90  END TRY91  BEGIN CATCH92    SELECT93      @LogMessage = CONCAT(' executing: ',94      @SQLcmd1,95      ' ',96      @SQLcmd2)97    EXEC QBM_PSessionErrorAdd DEFAULT,98      @LogMessage99    RAISERROR('',100    18,101    1)102      WITH NOWAIT103  END CATCH104END

Open raw exported source

1     create   procedure QBM_PSetRowLockOnly (@TablePattern nvarchar(64) = '%' ) as begin  declare @SQLcmd1 nvarchar(1024) declare @SQLcmd2 nvarchar2(1024) declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @DebugSwitch int = 0 Declare @LogMessage nvarchar3(max) SET XACT_ABORT OFF BEGIN TRY set nocount on insert into @ElementBuffer(ContentShort , ContentFull ) select distinct case i.allow_row_locks    when4 0 then concat('ALTER INDEX ', isnull(i.name, 'ALL'), ' on ', t.name , ' SET (ALLOW_ROW_LOCKS = ON)') else '' end , case i.allow_page_locks    when 1 then5 concat('ALTER INDEX ', isnull(i.name, 'ALL'), ' on ', t.name , ' SET (ALLOW_PAGE_LOCKS = OFF)') else '' end  from sys.tables t join sys.schemas o on t.schema_id6 = o.schema_id and dbo.QBM_FGIObjectIsDropable (o.schema_id)=1 join sys.indexes i on i.object_id = t.object_id where t.type = 'U' and t.name like @TablePattern7 and t.is_memory_optimized = 0 and i.is_hypothetical = 0  and i.type not in (3,4,5,6) and t.name not like 'sys%'  and t.name not like 'ms%' and t.name 8not like 'IH%' and t.name not like 'conflict%' and (i.allow_row_locks = 0 or i.allow_page_locks = 1 )  and i.type_desc in ('CLUSTERED', 'NONCLUSTERED',9 'HEAP')  and not ( i.type_desc = 'HEAP' and exists ( select top 1 1 from sys.indexes ii where i.object_id = ii.object_id and ii.type_desc in ('CLUSTERED COLUMNSTORE'10, 'NONCLUSTERED COLUMNSTORE') ) )  and o.name = 'dbo' order by 1 select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount11 begin select top 1 @SQLcmd1 = bu.ContentShort , @SQLcmd2 = bu.ContentFull from @ElementBuffer bu where bu.ElementIndex = @ElementIndex if @DebugSwitch12 > 0 begin print @SQLcmd1 print @SQLcmd2 end if @SQLcmd1 > ' ' begin exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd1 , @LockTimeout_ms = default13 , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 , @ExecuteWithTransact = 0 end if @SQLcmd2 > ' ' begin exec 14QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd2 , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent15 = 0 , @ExecuteWithTransact = 0 end select @ElementIndex += 1 end  END TRY BEGIN CATCH select @LogMessage = concat(' executing: ', @SQLcmd1, ' ' , @SQLcmd216) exec QBM_PSessionErrorAdd default, @LogMessage RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end 17