Back to OIM Explorer

dbo.QBM_PIndexCreate

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.389 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_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PIndexDrop source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_FTIndexesForColumns source text reference

References

Referenced By

Complete Source

SQL208 lines
1CREATE PROCEDURE QBM_PIndexCreate(2  @Tablename varchar(30),3  @IndexName varchar(30),4  @IsUnique BIT,5  @Columns varchar(max),6  @Includes varchar(max) = ''7)8AS9BEGIN10  DECLARE @existing TABLE(IndexName varchar(256) collate database_default,11  IsPK BIT,12  IsUnique BIT,13  HasMoreColumns BIT,14  HasAllIncludes BIT,15  HasMoreIncludes BIT)16  DECLARE @SqlCmd nvarchar(max)17  DECLARE @Pattern nvarchar(max)18  DECLARE @DebugSwitch int = 019  DECLARE @DebugExistingname varchar(30)20  DECLARE @Message nvarchar(1000)21  SET XACT_ABORT OFF22  BEGIN TRY23    IF NOT EXISTS(24      SELECT TOP 1 125      FROM sys.tables t26      WHERE27        t.name = @Tablename AND t.is_memory_optimized = 0)28    BEGIN29      GOTO endlabel30    END31    IF EXISTS(32      SELECT TOP 1 133      FROM sys.tables t34      WHERE35        t.name = 'TMPExcessQuantity')36    BEGIN37      SELECT38        @SqlCmd = CONCAT('update TMPExcessQuantity39		set Touched = 140		where ObjectClass = ''Index''41		and TableName = ''',42        @Tablename,43        '''44		and ElementName = ''',45        @IndexName,46        ''' ')47      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,48        @LockTimeout_ms = 250,49        @MaxWaitTimeForLock_s = 1,50        @ProcIDForJournal = @@procid,51        @HandleErrorSilent = 1,52        @ExecuteWithTransact = 053    END54    SELECT55      @Pattern = CONCAT('create ',56    CASE @IsUnique57      WHEN 1 THEN58    'unique'59    ELSE ''60    END,61    ' index "@IndexName" on @Tablename (@Columns) ')62    IF @Includes > ' '63    BEGIN64      SELECT @Pattern = @Pattern + '	include (@Includes)'65    END66    SELECT67      @Pattern = @Pattern + ' with (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS =  OFF )'68    INSERT INTO @existing(IndexName,69    IsPK,70    IsUnique,71    HasMoreColumns,72    HasAllIncludes,73    HasMoreIncludes)74    SELECT75      e.IndexName,76      e.IsPK,77      e.IsUnique,78      e.HasMoreColumns,79      e.HasAllIncludes,80      e.HasMoreIncludes81    FROM dbo.QBM_FTIndexesForColumns(@TableName,82    @Columns,83    @Includes) e84    SELECT85      TOP 1 @Message = CONCAT('#LDS#Indexname {0} is too long.|',86      e.Indexname,87      '|')88    FROM @existing e89    WHERE90      len(e.IndexName) > 3091    IF @Message > ' '92    BEGIN93      RAISERROR(@message,94      18,95      1)96        WITH nowait97    END98    IF EXISTS(99      SELECT TOP 1 1100      FROM @existing e101      WHERE102        e.IndexName = @IndexName AND e.HasMoreColumns = 0 AND e.HasAllIncludes = 1 AND e.HasMoreIncludes = 0 AND e.IsUnique = @IsUnique)103    BEGIN104      IF EXISTS(105        SELECT TOP 1 1106        FROM @existing e107        WHERE108          e.IndexName <> @IndexName AND(e.IsUnique = @IsUnique OR @IsUnique = 0) AND e.HasAllIncludes = 1 AND @IndexName NOT IN('QBM_XI7JobQueue'109      , 'SAC_XC1SAPGroupHasAuthObjectEl'))110      BEGIN111        IF @DebugSwitch > 0112        BEGIN113          print 'Fall 1b - dropppen'114        END115        EXEC QBM_PIndexDrop @Tablename,116          @Indexname117        GOTO endLabel118      END119      ELSE120      BEGIN121        IF @DebugSwitch > 0122        BEGIN123          print 'Fall 1a - nichts zu tun'124        END125        GOTO endLabel126      END127    END128    IF EXISTS(129      SELECT TOP 1 1130      FROM @existing e131      WHERE132        e.IndexName = @IndexName AND(e.IsUnique <> @IsUnique OR e.HasAllIncludes = 0 OR e.HasMoreColumns = 1 OR e.HasMoreIncludes = 1))133    BEGIN134      IF @DebugSwitch > 0135      BEGIN136        print 'Fall 2 - erst mal dropppen'137      END138      EXEC QBM_PIndexDrop @Tablename,139        @Indexname140    END141    IF NOT EXISTS(142      SELECT TOP 1 1143      FROM @existing e144      WHERE145        e.IndexName = @IndexName) AND EXISTS(146    SELECT TOP 1 1147    FROM sys.indexes i148    JOIN sys.tables t149      ON i.object_id = t.object_id150    WHERE151      i.name = @IndexName AND t.name = @Tablename AND i.is_hypothetical = 0 AND i.type NOT IN(3, 4, 5,152    6))153    BEGIN154      IF @DebugSwitch > 0155      BEGIN156        print 'Fall 2a - erst mal dropppen'157      END158      EXEC QBM_PIndexDrop @Tablename,159        @Indexname160    END161    SELECT @DebugExistingname = NULL162    SELECT TOP 1 @DebugExistingname = e.IndexName163    FROM @existing e164    WHERE165      (e.IsUnique = @IsUnique OR @IsUnique = 0) AND e.HasAllIncludes = 1 AND e.IndexName <> @IndexName AND @IndexName NOT IN('QBM_XI7JobQueue'166    ,167    'SAC_XC1SAPGroupHasAuthObjectEl')168    IF @DebugExistingname > ' '169    BEGIN170      IF @DebugSwitch > 0171      BEGIN172        print 'Fall 3 - ' + @IndexName + ' ist Teilmenge von existing ' + @DebugExistingname + '(' + @columns173      END174      GOTO endLabel175    END176    IF @DebugSwitch > 0177    BEGIN178      print 'Fall 4, durchläufer'179      SELECT *180      FROM @existing181    END182    CreateIt:183    SELECT184      @SqlCmd = replace(replace(replace(replace(@pattern, '@indexname', @IndexName), '@Tablename', @Tablename),185      '@Columns', @Columns),186      '@Includes',187      @Includes)188    IF @DebugSwitch > 0189    BEGIN190      print 'anlegen' print @sqlcmd191    END192    EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,193      @LockTimeout_ms = 2500,194      @MaxWaitTimeForLock_s = 10,195      @ProcIDForJournal = @@procid,196      @HandleErrorSilent = 0,197      @ExecuteWithTransact = 0198  END TRY199  BEGIN CATCH200    EXEC QBM_PSessionErrorAdd DEFAULT201    RAISERROR('',202    18,203    1)204      WITH NOWAIT205  END CATCH206  endLabel:207  RETURN208END
Open raw exported source
SQL · Raw33 lines
1    create   procedure QBM_PIndexCreate (@Tablename varchar(30) , @IndexName varchar(30) , @IsUnique bit , @Columns varchar(max)  , @Includes varchar2(max) = ''  ) as begin declare @existing table (IndexName varchar(256) collate database_default  , IsPK bit , IsUnique bit , HasMoreColumns bit , HasAllIncludes3 bit , HasMoreIncludes bit ) declare @SqlCmd nvarchar(max) declare @Pattern nvarchar(max) declare @DebugSwitch int = 0 declare @DebugExistingname varchar4(30) declare @Message nvarchar(1000) SET XACT_ABORT OFF BEGIN TRY if not exists (select top 1 1 from sys.tables t where t.name = @Tablename and t.is_memory_optimized5 = 0 ) begin goto endlabel end if exists (select top 1 1 from sys.tables t where t.name = 'TMPExcessQuantity' ) begin select @SqlCmd = concat( 'update TMPExcessQuantity6		set Touched = 17		where ObjectClass = ''Index''8		and TableName = '''9, @Tablename, '''10		and ElementName = ''', @IndexName, ''' ' ) exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms = 250 , @MaxWaitTimeForLock_s11 = 1 , @ProcIDForJournal = @@procid , @HandleErrorSilent = 1  , @ExecuteWithTransact = 0 end select @Pattern = concat('create ' , case @IsUnique when 112 then 'unique' else '' end , ' index "@IndexName" on @Tablename (@Columns) ' ) if @Includes > ' ' begin select @Pattern = @Pattern + '	include (@Includes)'13 end select @Pattern = @Pattern + ' with (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS =  OFF )' insert into @existing(IndexName, IsPK, IsUnique, HasMoreColumns14, HasAllIncludes, HasMoreIncludes) select e.IndexName, e.IsPK, e.IsUnique, e.HasMoreColumns, e.HasAllIncludes, e.HasMoreIncludes from dbo.QBM_FTIndexesForColumns15 (@TableName, @Columns , @Includes ) e select top 1 @Message = concat('#LDS#Indexname {0} is too long.|', e.Indexname, '|') from @existing e where len(e.IndexName16) > 30 if @Message > ' ' begin raiserror(@message, 18, 1) with nowait end if exists (select top 1 1 from @existing e where e.IndexName = @IndexName  and17 e.HasMoreColumns = 0 and e.HasAllIncludes = 1 and e.HasMoreIncludes = 0 and e.IsUnique = @IsUnique ) begin  if exists(select top 1 1 from @existing e 18where e.IndexName <> @IndexName and (e.IsUnique = @IsUnique or @IsUnique = 0)  and e.HasAllIncludes = 1   and @IndexName not in ( 'QBM_XI7JobQueue' ,'SAC_XC1SAPGroupHasAuthObjectEl'19 ) ) begin  if @DebugSwitch > 0 begin print 'Fall 1b - dropppen' end exec QBM_PIndexDrop @Tablename, @Indexname goto endLabel end else begin  if @DebugSwitch20 > 0 begin print 'Fall 1a - nichts zu tun' end goto endLabel end end if exists (select top 1 1 from @existing e where e.IndexName = @IndexName and (e.IsUnique21 <> @IsUnique or e.HasAllIncludes = 0 or e.HasMoreColumns = 1 or e.HasMoreIncludes = 1 ) ) begin  if @DebugSwitch > 0 begin print 'Fall 2 - erst mal dropppen'22 end exec QBM_PIndexDrop @Tablename, @Indexname end if not exists (select top 1 1 from @existing e where e.IndexName = @IndexName ) and exists (select 23top 1 1 from sys.indexes i join sys.tables t on i.object_id = t.object_id where i.name = @IndexName and t.name = @Tablename  and i.is_hypothetical = 0 24 and i.type not in (3,4,5,6) ) begin  if @DebugSwitch > 0 begin print 'Fall 2a - erst mal dropppen' end exec QBM_PIndexDrop @Tablename, @Indexname end 25 select @DebugExistingname = null select top 1 @DebugExistingname = e.IndexName from @existing e where (e.IsUnique = @IsUnique or @IsUnique = 0)  and e.HasAllIncludes26 = 1  and e.IndexName <> @IndexName  and @IndexName not in ( 'QBM_XI7JobQueue' ,'SAC_XC1SAPGroupHasAuthObjectEl' ) if @DebugExistingname > ' ' begin if27 @DebugSwitch > 0 begin print 'Fall 3 - ' + @IndexName + ' ist Teilmenge von existing ' + @DebugExistingname + '(' + @columns end goto endLabel end   if28 @DebugSwitch > 0 begin print 'Fall 4, durchläufer' select * from @existing end CreateIt: select @SqlCmd = replace(replace(replace(replace(@pattern , '@indexname'29, @IndexName) , '@Tablename', @Tablename) , '@Columns', @Columns) , '@Includes', @Includes) if @DebugSwitch > 0 begin print 'anlegen' print @sqlcmd end30  exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms = 2500  , @MaxWaitTimeForLock_s = 10  , @ProcIDForJournal = @@procid , @HandleErrorSilent31 = 0  , @ExecuteWithTransact = 0 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: return end32 33