Back to OIM Explorer

dbo.QBM_PIndexDrop

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.254 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

SQL104 lines
1CREATE PROCEDURE QBM_PIndexDrop(2  @TablePattern nvarchar(255) = '',3  @indexPattern nvarchar(255) = ''4)5AS6BEGIN7  DECLARE @SQLcmd nvarchar(1024)8  DECLARE @DebugSwitch int = 09  DECLARE @ElementBufferIndex QBM_YCursorBuffer10  DECLARE @ElementCount int11  DECLARE @ElementIndex int12  SET XACT_ABORT OFF13  BEGIN TRY14    INSERT INTO @ElementBufferIndex(ContentFull)15    SELECT16      CONCAT('drop index if exists ',17      s.name,18      N '."',19      t.name,20      N '"."',21    CASE22      WHEN i.name LIKE '%"%' THEN23    N '[' + i.name + N ']'24    ELSE i.name25    END,26    '"')27    FROM sys.indexes i28      WITH(readpast)29    JOIN sys.tables t30      WITH(readpast)31      ON i.object_id=t.object_id32    JOIN sys.schemas s33      WITH(readpast)34      ON t.schema_id = s.schema_id35    WHERE36      i.is_primary_key = 0 AND i.is_hypothetical = 0 AND i.type NOT IN(3,37    4,38    5,39    6) AND i.type_desc <> 'HEAP' AND dbo.QBM_FGIObjectIsDropable(s.Schema_id) = 1 AND i.name LIKE @indexPattern AND t.name LIKE @TablePattern AND40    t.is_memory_optimized = 0 AND indexproperty(t.object_id,41    i.name,42    'IsStatistics') = 043    UNION all44    SELECT45      CONCAT('if exists(select top 1 1 46					from sys.stats with (readpast)47					where  name = ''',48      i.name,49      '''50				  )51					 begin52						drop statistics ',53      s.name,54      N '."',55      t.name,56      N '"."',57      i.name,58      '"59					 end60					 ')61    FROM sys.stats i62      WITH(readpast)63    JOIN sys.tables t64      ON i.object_id=t.object_id65    JOIN sys.schemas s66      ON t.schema_id = s.schema_id67    LEFT68    OUTER69    JOIN sys.indexes ii70      ON ii.object_id = t.object_id AND ii.name = i.name71    WHERE72      dbo.QBM_FGIObjectIsDropable(s.Schema_id) = 1 AND ii.name IS NULL AND i.name LIKE @indexPattern AND t.name LIKE @TablePattern AND t.is_memory_optimized73    = 0 AND indexproperty(t.object_id,74    i.name,75    'IsStatistics') = 176    SELECT @ElementCount = @@ROWCOUNT77    SELECT @ElementIndex = 178    WHILE @ElementIndex <= @ElementCount79    BEGIN80      SELECT TOP 1 @SQLcmd = bu.ContentFull81      FROM @ElementBufferIndex bu82      WHERE83        bu.ElementIndex = @ElementIndex84      IF @DebugSwitch > 085      BEGIN86        print @SQLcmd87      END88      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,89        @LockTimeout_ms = 2500,90        @MaxWaitTimeForLock_s = 10,91        @ProcIDForJournal = DEFAULT,92        @HandleErrorSilent = 0,93        @ExecuteWithTransact = 094      SELECT @ElementIndex += 195    END96  END TRY97  BEGIN CATCH98    EXEC QBM_PSessionErrorAdd DEFAULT99    RAISERROR('',100    18,101    1)102      WITH NOWAIT103  END CATCH104END
Open raw exported source
SQL ยท Raw23 lines
1  create   procedure QBM_PIndexDrop (@TablePattern nvarchar(255)= '', @indexPattern nvarchar(255) = '')  as begin  declare @SQLcmd nvarchar(10242) declare @DebugSwitch int = 0 declare @ElementBufferIndex QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int  SET XACT_ABORT OFF BEGIN3 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 join5 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_desc6 <> '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					 begin13						drop statistics ' , s.name , N'."' , t.name , N'"."' , i.name , '"14					 end15					 ' ) from sys.stats i with16 (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_id17 = 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 like18 @TablePattern and t.is_memory_optimized = 0 and indexproperty(t.object_id, i.name, 'IsStatistics') = 1 select @ElementCount = @@ROWCOUNT select @ElementIndex19 = 1 while @ElementIndex <= @ElementCount begin select top 1 @SQLcmd = bu.ContentFull from @ElementBufferIndex bu where bu.ElementIndex = @ElementIndex20 if @DebugSwitch > 0 begin print @SQLcmd end  exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms = 2500  , @MaxWaitTimeForLock_s21 = 10 , @ProcIDForJournal = default , @HandleErrorSilent = 0  , @ExecuteWithTransact = 0 select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd22 default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end 23