dbo.QBM_PIndexDisable

1  create   procedure QBM_PIndexDisable ( @tablepattern nvarchar(64) = '%' , @IndexPattern nvarchar(64) = '%' , @PrimaryKeyAlso bit = 0 , @UniqueOnly
2 bit = 0 ) as begin declare @TableName varchar(30) declare @indexname varchar(30) declare @SQLCmd nvarchar(max) declare @DebugSwitch int = 0 SET XACT_ABORT
3 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 DialogTable
5 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 @PrimaryKeyAlso
6 = 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 null
7 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_s
9 = 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_desc
11 = '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 print
14 @sqlcmd end exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @sqlcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal
15 = 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