Back to OIM Explorer

dbo.QBM_PIndexDropRedundant_i

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 1.431 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_FSQIndexDef source text reference
  • references source dbo.QBM_PIndexDrop source text reference
  • references source dbo.QBM_PIndexDropRedundant source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL78 lines
1CREATE PROCEDURE QBM_PIndexDropRedundant_i(2  @TableName nvarchar(64)3)4AS5BEGIN6  DECLARE @SQLcmd nvarchar(max)7  DECLARE @DebugSwitch int = 08  DECLARE @ElementBuffer QBM_YCursorBuffer9  DECLARE @ElementCount int10  DECLARE @ElementIndex int11  SET XACT_ABORT OFF12  BEGIN TRY13    SET nocount14      ON15    INSERT INTO @ElementBuffer(ContentFull)16    SELECT17      dbo.QBM_FSQIndexDef(@tablename,18      y.indexname)19    FROM(20    SELECT21      i.name AS indexname,22    CASE23      WHEN i.is_primary_key = 1 THEN24    N 'P'25      WHEN i.Type = 1 THEN26    'P'27      WHEN i.is_unique = 1 THEN28    N 'U'29    ELSE N '0'30    END AS Indexmarker,31    CASE32      WHEN i.name LIKE 'PK[_]%' THEN33    'Z'34      WHEN i.name LIKE 'GEN[_]X%' THEN35    'X'36      WHEN i.name LIKE '[^C][^C][^C][_]X%' THEN37    'Y'38    ELSE 'A'39    END AS SortOrder40    FROM sys.tables t41      WITH(readpast)42    JOIN sys.indexes i43      WITH(readpast)44      ON i.object_id = t.object_id45    WHERE46      t.name = @TableName AND t.is_memory_optimized = 0 AND i.is_hypothetical = 0 AND i.type NOT IN(3,47    4, 5, 6)) AS y48    WHERE49      y.Indexmarker NOT IN('P')50    ORDER BY y.SortOrder,51    y.Indexmarker,52    y.indexname53    SELECT @ElementCount = @@ROWCOUNT54    SELECT @ElementIndex = 155    WHILE @ElementIndex <= @ElementCount56    BEGIN57      SELECT TOP 1 @SQLcmd = bu.ContentFull58      FROM @ElementBuffer bu59      WHERE60        bu.ElementIndex = @ElementIndex61      IF @DebugSwitch > 062      BEGIN63        print @sqlcmd64      END65      EXEC sp_executesql @SQLcmd66      SELECT @ElementIndex += 167    END68  END TRY69  BEGIN CATCH70    EXEC QBM_PSessionErrorAdd DEFAULT71    RAISERROR('',72    18,73    1)74      WITH NOWAIT75  END CATCH76  ende:77  RETURN78END
Open raw exported source
SQL ยท Raw11 lines
1  create   procedure QBM_PIndexDropRedundant_i ( @TableName nvarchar(64) ) as begin declare @SQLcmd nvarchar(max) declare @DebugSwitch int = 0 declare2 @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int SET XACT_ABORT OFF BEGIN TRY set nocount on   insert into @ElementBuffer3 (ContentFull) select   dbo.QBM_FSQIndexDef(@tablename, y.indexname) from ( select i.name as indexname, case when i.is_primary_key = 1 then N'P' when i.Type4 = 1 then 'P'  when i.is_unique = 1 then N'U' else N'0' end as Indexmarker ,case when i.name like 'PK[_]%' then 'Z'  when i.name like 'GEN[_]X%' then 'X'5  when i.name like '[^C][^C][^C][_]X%' then 'Y'  else 'A' end as SortOrder from sys.tables t with (readpast) join sys.indexes i with (readpast) on i.object_id6 = t.object_id where t.name = @TableName and t.is_memory_optimized = 0  and i.is_hypothetical = 0  and i.type not in (3,4,5,6) ) as y where y.Indexmarker7 not in ('P') order by y.SortOrder, y.Indexmarker, y.indexname select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount8 begin select top 1 @SQLcmd = bu.ContentFull from @ElementBuffer bu where bu.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print @sqlcmd end exec9 sp_executesql @SQLcmd select @ElementIndex += 1 end   END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH10 ende:  return end 11