Back to OIM Explorer

dbo.QBM_PIndexCreateGenerated_XA

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.854 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_FGICodeName source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PIndexCreate source text reference
  • references source dbo.QBM_PIndexCreateGenerated source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL214 lines
1CREATE PROCEDURE QBM_PIndexCreateGenerated_XA(2  @TableName varchar(30)3)4AS5BEGIN6  DECLARE @ColumnName varchar(30)7  DECLARE @Indexname varchar(30)8  DECLARE @ColumnList nvarchar(max)9  DECLARE @IncludeList nvarchar(max)10  DECLARE @IsInclude BIT11  DECLARE @UID_QBMUniqueGroup varchar(38)12  DECLARE @UID_QBMUniqueGroup_alt varchar(38)13  DECLARE @ElementBufferMulti QBM_YCursorBuffer14  DECLARE @ElementCount int15  DECLARE @ElementIndex int16  DECLARE @DebugSwitch int = 017  SET XACT_ABORT OFF18  BEGIN TRY19    SELECT @UID_QBMUniqueGroup_alt = '#'20    SELECT @ColumnList = ''21    SELECT @IncludeList = ''22    INSERT INTO @ElementBufferMulti(UID1,23    Ident2,24    Bit1)25    SELECT26      x.UID_QBMUniqueGroup,27      x.ColumnName,28      x.IsInclude29    FROM(30    SELECT31      ug.UID_QBMUniqueGroup,32      tc.TableName,33      c.ColumnName,34      convert(int, c.IsPKMember) + convert(int, c.IsForeignKey) AS KeyMember,35    CASE36      WHEN c.SchemaDataLen > 800 THEN37    138    ELSE 039    END AS IsInclude40    FROM QBMUniqueGroup ug41      WITH(readpast)42    JOIN QBMUniqueGroupHasColumn ghc43      WITH(readpast)44      ON ug.UID_QBMUniqueGroup = ghc.UID_QBMUniqueGroup45    JOIN DialogTable tc46      WITH(readpast)47      ON tc.UID_DialogTable = ug.UID_DialogTable48    JOIN DialogColumn c49      WITH(readpast)50      ON c.XObjectKey = ghc.ObjectKeyDialogColumn AND tc.TableType IN('B', 'T') AND c.SchemaDataLen <> -151    WHERE52      tc.TableName = @TableName53    UNION54    SELECT55      ug.UID_QBMUniqueGroup,56      tb.TableName,57      cb.ColumnName,58      convert(int, cb.IsPKMember) + convert(int, cb.IsForeignKey) AS KeyMemeber,59    CASE60      WHEN c.SchemaDataLen > 800 THEN61    162    ELSE 063    END AS IsInclude64    FROM QBMUniqueGroup ug65      WITH(readpast)66    JOIN QBMUniqueGroupHasColumn ghc67      WITH(readpast)68      ON ug.UID_QBMUniqueGroup = ghc.UID_QBMUniqueGroup69    JOIN DialogTable tc70      WITH(readpast)71      ON tc.UID_DialogTable = ug.UID_DialogTable72    JOIN DialogColumn c73      WITH(readpast)74      ON c.XObjectKey = ghc.ObjectKeyDialogColumn AND tc.TableType IN('V')75    JOIN DialogTable tb76      WITH(readpast)77      ON tc.UID_DialogTableBase = tb.UID_DialogTable78    JOIN DialogColumn cb79      WITH(readpast)80      ON c.UID_BaseColumn = cb.UID_DialogColumn AND cb.SchemaDataLen <> -181    WHERE82      tb.TableName = @TableName83    UNION84    SELECT85      tc.UID_DialogTable,86      tc.TableName,87      c.ColumnName,88      convert(int, c.IsPKMember) + convert(int, c.IsForeignKey) AS KeyMemeber,89    CASE90      WHEN c.SchemaDataLen > 800 THEN91    192    ELSE 093    END AS IsInclude94    FROM DialogTable tc95      WITH(readpast)96    JOIN DialogColumn c97      WITH(readpast)98      ON c.UID_DialogTable = tc.UID_DialogTable AND tc.TableType IN('B', 'T') AND c.IsAlternatePKMember = 1 AND c.SchemaDataLen <> -199    WHERE100      tc.TableName = @TableName) AS x101    JOIN sys.tables ts102      WITH(nolock)103      ON x.TableName = ts.name collate database_default AND ts.type = 'U'104    JOIN sys.schemas s105      WITH(nolock)106      ON ts.schema_id = s.schema_id AND s.name = 'dbo'107    JOIN sys.columns cs108      WITH(nolock)109      ON ts.object_id = cs.object_id AND cs.name = x.ColumnName collate database_default110    WHERE111      NOT EXISTS(112    SELECT TOP 1 1113    FROM QBM_VHeavyLoadTables h114    WHERE115      h.TableName = x.TableName AND h.IsNoAutoIndex = 1)116    ORDER BY x.UID_QBMUniqueGroup,117    x.TableName,118    x.KeyMember DESC,119    x.ColumnName120    SELECT @ElementCount = @@ROWCOUNT121    SELECT @ElementIndex = 1122    WHILE @ElementIndex <= @ElementCount123    BEGIN124      SELECT125        TOP 1 @UID_QBMUniqueGroup = bu.UID1,126        @columnname = bu.Ident2,127        @IsInclude = bu.Bit1128      FROM @ElementBufferMulti bu129      WHERE130        bu.ElementIndex = @ElementIndex131      IF @DebugSwitch > 0132      BEGIN133        print @UID_QBMUniqueGroup + '.' + @TableName + '.' + @columnname + '  ' + @ColumnList + '  ' + @IncludeList + str(@IsInclude)134      END135      IF @UID_QBMUniqueGroup <> @UID_QBMUniqueGroup_alt136      BEGIN137        IF @UID_QBMUniqueGroup_alt <> '#'138        BEGIN139          SELECT140            @Indexname = dbo.QBM_FGICodeName('XA',141            'uniqueGroup' + @UID_QBMUniqueGroup_alt + @Tablename + @ColumnList + @IncludeList)142          IF @IncludeList > ' '143          BEGIN144            SELECT145              @IncludeList = substring(@IncludeList,146              2,147              len(@IncludeList) -1)148          END149          IF @ColumnList > ' '150          BEGIN151            SELECT152              @ColumnList = substring(@ColumnList,153              2,154              len(@ColumnList) -1)155            EXEC QBM_PIndexCreate @Tablename,156              @Indexname,157            0,158              @ColumnList,159              @IncludeList160          END161          SELECT @ColumnList = ''162          SELECT @IncludeList = ''163        END164        SELECT @UID_QBMUniqueGroup_alt = @UID_QBMUniqueGroup165      END166      IF @IsInclude = 0167      BEGIN168        SELECT169          @ColumnList = @ColumnList + ', ' + @ColumnName170      END171      ELSE172      BEGIN173        SELECT174          @IncludeList = @IncludeList + ', ' + @ColumnName175      END176      SELECT @ElementIndex += 1177    END178    IF @ColumnList > ' '179    BEGIN180      SELECT181        @Indexname = dbo.QBM_FGICodeName('XA',182        'uniqueGroup' + @UID_QBMUniqueGroup_alt + @Tablename + @ColumnList + @IncludeList)183      IF @IncludeList > ' '184      BEGIN185        SELECT186          @IncludeList = substring(@IncludeList,187          2,188          len(@IncludeList) -1)189      END190      IF @ColumnList > ' '191      BEGIN192        SELECT193          @ColumnList = substring(@ColumnList,194          2,195          len(@ColumnList) -1)196        EXEC QBM_PIndexCreate @Tablename,197          @Indexname,198        0,199          @ColumnList,200          @IncludeList201      END202    END203  END TRY204  BEGIN CATCH205    EXEC QBM_PSessionErrorAdd DEFAULT206    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()207    RAISERROR(@Rethrow,208    18,209    1)210      WITH NOWAIT211  END CATCH212  ende:213  RETURN214END
Open raw exported source
SQL ยท Raw33 lines
1  create   procedure QBM_PIndexCreateGenerated_XA (@TableName varchar(30) ) as begin declare @ColumnName varchar(30) declare @Indexname varchar(302) declare @ColumnList nvarchar(max) declare @IncludeList nvarchar(max) declare @IsInclude bit declare @UID_QBMUniqueGroup varchar(38) declare @UID_QBMUniqueGroup_alt3 varchar(38)  declare @ElementBufferMulti QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @DebugSwitch int = 0 SET XACT_ABORT4 OFF BEGIN TRY select @UID_QBMUniqueGroup_alt = '#' select @ColumnList = '' select @IncludeList = ''  insert into @ElementBufferMulti (UID1, Ident2, Bit15) select x.UID_QBMUniqueGroup, x.ColumnName, x.IsInclude from (  select ug.UID_QBMUniqueGroup, tc.TableName, c.ColumnName, convert(int, c.IsPKMember) +6 convert(int, c.IsForeignKey) as KeyMember ,case when c.SchemaDataLen > 800 then 1 else 0 end as IsInclude from QBMUniqueGroup ug with (readpast) join 7QBMUniqueGroupHasColumn ghc with (readpast) on ug.UID_QBMUniqueGroup = ghc.UID_QBMUniqueGroup join DialogTable tc with (readpast) on tc.UID_DialogTable8 = ug.UID_DialogTable join DialogColumn c with (readpast) on c.XObjectKey = ghc.ObjectKeyDialogColumn and tc.TableType in ('B', 'T') and c.SchemaDataLen9 <> -1  where tc.TableName = @TableName union  select ug.UID_QBMUniqueGroup, tb.TableName, cb.ColumnName, convert(int, cb.IsPKMember) + convert(int, cb.IsForeignKey10) as KeyMemeber ,case when c.SchemaDataLen > 800 then 1 else 0 end as IsInclude from QBMUniqueGroup ug with (readpast) join QBMUniqueGroupHasColumn ghc11 with (readpast) on ug.UID_QBMUniqueGroup = ghc.UID_QBMUniqueGroup join DialogTable tc with (readpast) on tc.UID_DialogTable = ug.UID_DialogTable join 12DialogColumn c with (readpast) on c.XObjectKey = ghc.ObjectKeyDialogColumn and tc.TableType in ('V') join DialogTable tb with (readpast) on tc.UID_DialogTableBase13 = tb.UID_DialogTable join DialogColumn cb with (readpast) on c.UID_BaseColumn = cb.UID_DialogColumn and cb.SchemaDataLen <> -1  where tb.TableName = @TableName14 union  select tc.UID_DialogTable, tc.TableName, c.ColumnName, convert(int, c.IsPKMember) + convert(int, c.IsForeignKey) as KeyMemeber ,case when c.SchemaDataLen15 > 800 then 1 else 0 end as IsInclude from DialogTable tc with (readpast) join DialogColumn c with (readpast) on c.UID_DialogTable = tc.UID_DialogTable16 and tc.TableType in ('B', 'T') and c.IsAlternatePKMember = 1 and c.SchemaDataLen <> -1  where tc.TableName = @TableName ) as x  join sys.tables ts with17 (nolock) on x.TableName = ts.name collate database_default and ts.type = 'U'  join sys.schemas s with (nolock) on ts.schema_id = s.schema_id and s.name18 = 'dbo' join sys.columns cs with (nolock) on ts.object_id = cs.object_id and cs.name = x.ColumnName collate database_default where not exists (Select 19top 1 1 from QBM_VHeavyLoadTables h where h.TableName = x.TableName and h.IsNoAutoIndex = 1 ) order by x.UID_QBMUniqueGroup, x.TableName, x.KeyMember desc20 , x.ColumnName select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @UID_QBMUniqueGroup 21= bu.UID1 , @columnname = bu.Ident2 , @IsInclude = bu.Bit1 from @ElementBufferMulti bu where bu.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin 22print @UID_QBMUniqueGroup + '.' + @TableName + '.' + @columnname + '  ' + @ColumnList + '  ' + @IncludeList + str(@IsInclude) end if @UID_QBMUniqueGroup23 <> @UID_QBMUniqueGroup_alt begin if @UID_QBMUniqueGroup_alt <> '#' begin select @Indexname = dbo.QBM_FGICodeName('XA', 'uniqueGroup' + @UID_QBMUniqueGroup_alt24 + @Tablename + @ColumnList + @IncludeList) if @IncludeList > ' ' begin select @IncludeList = substring(@IncludeList, 2, len(@IncludeList) -1 ) end if 25@ColumnList > ' ' begin select @ColumnList = substring(@ColumnList, 2, len(@ColumnList) -1 ) exec QBM_PIndexCreate @Tablename, @Indexname, 0, @ColumnList26, @IncludeList end select @ColumnList = '' select @IncludeList = '' end  select @UID_QBMUniqueGroup_alt = @UID_QBMUniqueGroup end if @IsInclude = 0 begin27 select @ColumnList = @ColumnList + ', ' + @ColumnName end else begin select @IncludeList = @IncludeList + ', ' + @ColumnName end select @ElementIndex 28+= 1 end  if @ColumnList > ' ' begin select @Indexname = dbo.QBM_FGICodeName('XA', 'uniqueGroup' + @UID_QBMUniqueGroup_alt + @Tablename + @ColumnList +29 @IncludeList) if @IncludeList > ' ' begin select @IncludeList = substring(@IncludeList, 2, len(@IncludeList) -1 ) end if @ColumnList > ' ' begin select30 @ColumnList = substring(@ColumnList, 2, len(@ColumnList) -1 ) exec QBM_PIndexCreate @Tablename, @Indexname, 0, @ColumnList, @IncludeList end end END TRY31 BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT32 END CATCH ende: return end 33