dbo.QBM_PIndexDrop

1  create   procedure QBM_PIndexDrop (@TablePattern nvarchar(255)= '', @indexPattern nvarchar(255) = '')  as begin  declare @SQLcmd nvarchar(1024
2) declare @DebugSwitch int = 0 declare @ElementBufferIndex QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int  SET XACT_ABORT OFF BEGIN
3 TRY insert into @ElementBufferIndex(ContentFull) select CONCAT( 'drop index if exists ' , s.name , N'."' , t.name , N'"."' , case when i.name like '%"%'
4 then N'[' + i.name + N']' else i.name end , '"' ) from sys.indexes i with (readpast) join sys.tables t with (readpast) on i.object_id=t.object_id join
5 sys.schemas s with (readpast) on t.schema_id = s.schema_id where i.is_primary_key = 0 and i.is_hypothetical = 0  and i.type not in (3,4,5,6) and i.type_desc
6 <> 'HEAP' and dbo.QBM_FGIObjectIsDropable (s.Schema_id) = 1 and i.name like @indexPattern and t.name like @TablePattern and t.is_memory_optimized = 0 
7and indexproperty(t.object_id, i.name, 'IsStatistics') = 0 union all  select CONCAT( 'if exists(select top 1 1 
8					from sys.stats with (readpast)
9					where  name = '''
10, i.name , '''
11				  )
12					 begin
13						drop statistics ' , s.name , N'."' , t.name , N'"."' , i.name , '"
14					 end
15					 ' ) from sys.stats i with
16 (readpast) join sys.tables t on i.object_id=t.object_id join sys.schemas s on t.schema_id = s.schema_id left outer join sys.indexes ii on ii.object_id
17 = t.object_id and ii.name = i.name where dbo.QBM_FGIObjectIsDropable (s.Schema_id) = 1 and ii.name is null and i.name like @indexPattern and t.name like
18 @TablePattern and t.is_memory_optimized = 0 and indexproperty(t.object_id, i.name, 'IsStatistics') = 1 select @ElementCount = @@ROWCOUNT select @ElementIndex
19 = 1 while @ElementIndex <= @ElementCount begin select top 1 @SQLcmd = bu.ContentFull from @ElementBufferIndex bu where bu.ElementIndex = @ElementIndex
20 if @DebugSwitch > 0 begin print @SQLcmd end  exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms = 2500  , @MaxWaitTimeForLock_s
21 = 10 , @ProcIDForJournal = default , @HandleErrorSilent = 0  , @ExecuteWithTransact = 0 select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd
22 default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end 
23