dbo.QBM_PTableLockEscalationSet

1  create   procedure QBM_PTableLockEscalationSet(@TablePattern varchar(64) ) as begin declare @tablename varchar(30) declare @SQLCmd nvarchar(max
2) declare @DebugSwitch int = 0 declare @AllNames QBM_YParameterList SET XACT_ABORT OFF BEGIN TRY if @@TRANCOUNT > 0 begin goto endLabel end select @SQLCmd
3 = '	select  t.name
4	from sys.tables t
5	where 
6		t.lock_escalation <> 1
7		%xtp% and t.is_memory_optimized = 0 
8		and t.type = ''U''
9		and t.is_ms_shipped = 0
10		and t.name not in ( 
11						''sysdiagrams''
12						)
13		and t.name like '''
14 + @TablePattern + '''
15
16	' if ISNULL(SERVERPROPERTY('IsXTPSupported'), 0) = 1 begin select @SQLCmd = replace(@SQLcmd, '%xtp%', '') end else begin select
17 @SQLCmd = replace(@SQLcmd, '%xtp%', '--') end if @DebugSwitch > 0 begin print @SQLCmd end insert into @AllNames(Parameter1) exec sp_executesql @SQLcmd
18 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  @AllNames
20 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 , @ProcIDForJournal
24 = @@procid , @HandleErrorSilent = 0 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow
25() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: return end 
26