dbo.QBM_PSetRowLockOnly

1     create   procedure QBM_PSetRowLockOnly (@TablePattern nvarchar(64) = '%' ) as begin  declare @SQLcmd1 nvarchar(1024) declare @SQLcmd2 nvarchar
2(1024) declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @DebugSwitch int = 0 Declare @LogMessage nvarchar
3(max) SET XACT_ABORT OFF BEGIN TRY set nocount on insert into @ElementBuffer(ContentShort , ContentFull ) select distinct case i.allow_row_locks    when
4 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 then
5 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_id
6 = 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 @TablePattern
7 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 <= @ElementCount
11 begin select top 1 @SQLcmd1 = bu.ContentShort , @SQLcmd2 = bu.ContentFull from @ElementBuffer bu where bu.ElementIndex = @ElementIndex if @DebugSwitch
12 > 0 begin print @SQLcmd1 print @SQLcmd2 end if @SQLcmd1 > ' ' begin exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd1 , @LockTimeout_ms = default
13 , @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 , @HandleErrorSilent
15 = 0 , @ExecuteWithTransact = 0 end select @ElementIndex += 1 end  END TRY BEGIN CATCH select @LogMessage = concat(' executing: ', @SQLcmd1, ' ' , @SQLcmd2
16) exec QBM_PSessionErrorAdd default, @LogMessage RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end 
17