Back to OIM Explorer

dbo.QBM_PIndexCreateGenerated_XX

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

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

dbo.QBM_PIndexCreateGenerated

Complete Source

1CREATE PROCEDURE QBM_PIndexCreateGenerated_XX(2  @TableName varchar(30)3)4AS5BEGIN6  DECLARE @ColumnName varchar(30)7  DECLARE @HasXIsInEffect BIT8  DECLARE @HasXOrigin BIT9  DECLARE @HasInheritInfo BIT10  DECLARE @Indexname varchar(30)11  DECLARE @IncludeList nvarchar(max)12  DECLARE @DebugSwitch int = 013  DECLARE @ElementBufferMulti QBM_YCursorBuffer14  DECLARE @ElementCount int15  DECLARE @ElementIndex int16  SET XACT_ABORT OFF17  BEGIN TRY18    INSERT INTO @ElementBufferMulti(Ident2,19    Bit1,20    Int1,21    Int2)22    SELECT23      DISTINCT x.ColumnName,24      x.HasXIsInEffect,25      x.HasXOrigin,26      x.HasInheritInfo27    FROM(28    SELECT29      tc.TableName,30      c.ColumnName,31      sign(len(isnull(ce.UID_DialogColumn, ''))) AS HasXIsInEffect,32      sign(len(isnull(co.UID_DialogColumn, ''))) AS HasXOrigin,33      sign(len(isnull(ci.UID_DialogColumn, ''))) AS HasInheritInfo34    FROM QBM_VQBMRelation r35    JOIN DialogColumn c36      WITH(readpast)37      ON r.UID_ChildColumn = c.UID_DialogColumn38    JOIN DialogTable tc39      WITH(readpast)40      ON c.UID_DialogTable = tc.UID_DialogTable AND tc.TableType IN('B', 'T')41    LEFT42    OUTER43    JOIN DialogColumn co44      WITH(readpast)45      ON co.UID_DialogTable = tc.UID_DialogTable AND co.ColumnName = 'XOrigin'46    LEFT47    OUTER48    JOIN DialogColumn ce49      WITH(readpast)50      ON tc.UID_DialogTable = ce.UID_DialogTable AND ce.ColumnName = 'XIsInEffect'51    LEFT52    OUTER53    JOIN DialogColumn ci54      WITH(readpast)55      ON tc.UID_DialogTable = ci.UID_DialogTable AND ci.ColumnName = 'InheritInfo'56    WHERE57      r.IsMNRelation = 1 AND tc.TableName = @TableName58    UNION all59    SELECT60      tc.TableName,61      c.ColumnName,62      0 AS HasXIsInEffect,63      0 AS HasXOrigin,64      sign(len(isnull(ci.UID_DialogColumn, ''))) AS HasInheritInfo65    FROM QBM_VQBMRelation r66    JOIN DialogColumn c67      WITH(readpast)68      ON r.UID_ChildColumn = c.UID_DialogColumn69    JOIN DialogTable tc70      WITH(readpast)71      ON c.UID_DialogTable = tc.UID_DialogTable AND tc.TableType IN('B', 'T')72    LEFT73    OUTER74    JOIN DialogColumn co75      WITH(readpast)76      ON co.UID_DialogTable = tc.UID_DialogTable AND co.ColumnName = 'XOrigin'77    LEFT78    OUTER79    JOIN DialogColumn ce80      WITH(readpast)81      ON tc.UID_DialogTable = ce.UID_DialogTable AND ce.ColumnName = 'XIsInEffect'82    LEFT83    OUTER84    JOIN DialogColumn ci85      WITH(readpast)86      ON tc.UID_DialogTable = ci.UID_DialogTable AND ci.ColumnName = 'InheritInfo'87    WHERE88      r.IsMNRelation = 0 AND tc.TableName = @TableName89    UNION all90    SELECT91      tc.TableName,92      c.ColumnName,93      sign(len(isnull(ce.UID_DialogColumn, ''))) AS HasXIsInEffect,94      sign(len(isnull(co.UID_DialogColumn, ''))) AS HasXOrigin,95      sign(len(isnull(ci.UID_DialogColumn, ''))) AS HasInheritInfo96    FROM DialogTable tc97      WITH(readpast)98    JOIN QBM_VQBMRelation r99      ON r.UID_DialogTableChild = tc.UID_DialogTable AND tc.TableType IN('B', 'T')100    JOIN DialogColumn c101      WITH(readpast)102      ON c.UID_DialogColumn = r.UID_ChildColumn103    LEFT104    OUTER105    JOIN DialogColumn co106      WITH(readpast)107      ON co.UID_DialogTable = tc.UID_DialogTable AND co.ColumnName = 'XOrigin'108    LEFT109    OUTER110    JOIN DialogColumn ce111      WITH(readpast)112      ON tc.UID_DialogTable = ce.UID_DialogTable AND ce.ColumnName = 'XIsInEffect'113    LEFT114    OUTER115    JOIN DialogColumn ci116      WITH(readpast)117      ON tc.UID_DialogTable = ci.UID_DialogTable AND ci.ColumnName = 'InheritInfo'118    WHERE119      tc.IsMAllTable = 1 AND tc.TableName = @TableName) AS x120    JOIN sys.tables ts121      WITH(nolock)122      ON x.TableName = ts.name collate database_default AND ts.type = 'U'123    JOIN sys.schemas s124      WITH(nolock)125      ON ts.schema_id = s.schema_id AND s.name = 'dbo'126    JOIN sys.columns cs127      WITH(nolock)128      ON ts.object_id = cs.object_id AND cs.name = x.ColumnName collate database_default129    WHERE130      NOT EXISTS(131    SELECT TOP 1 1132    FROM QBM_VHeavyLoadTables h133    WHERE134      h.TableName = x.TableName AND h.IsNoAutoIndex = 1)135    ORDER BY 1,136    2137    SELECT @ElementCount = @@ROWCOUNT138    SELECT @ElementIndex = 1139    WHILE @ElementIndex <= @ElementCount140    BEGIN141      SELECT142        TOP 1 @columnname = bu.Ident2,143        @HasXIsInEffect = bu.Bit1,144        @HasXOrigin = bu.Int1,145        @HasInheritInfo = bu.Int2146      FROM @ElementBufferMulti bu147      WHERE148        bu.ElementIndex = @ElementIndex149      SELECT150        @Indexname = dbo.QBM_FGICodeName('XX',151        @TableName + @ColumnName)152      IF @DebugSwitch > 0153      BEGIN154        print @Indexname155      END156      SELECT @IncludeList = ''157      IF @HasXOrigin = 1158      BEGIN159        SELECT @IncludeList = 'Xorigin'160      END161      IF @HasXIsInEffect = 1162      BEGIN163        SELECT164          @IncludeList = @IncludeList + ', XIsInEffect'165      END166      IF @HasInheritInfo = 1167      BEGIN168        SELECT @IncludeList = 'InheritInfo'169      END170      IF @DebugSwitch > 0171      BEGIN172        print CONCAT(@ColumnName,173        '(',174        @IncludeList,175        ')')176      END177      EXEC QBM_PIndexCreate @TableName,178        @Indexname,179      0,180        @ColumnName,181        @IncludeList182      SELECT @ElementIndex += 1183    END184  END TRY185  BEGIN CATCH186    EXEC QBM_PSessionErrorAdd DEFAULT187    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()188    RAISERROR(@Rethrow,189    18,190    1)191      WITH NOWAIT192  END CATCH193  ende:194  RETURN195END

Open raw exported source

1    create   procedure QBM_PIndexCreateGenerated_XX (@TableName varchar(30) ) as begin declare @ColumnName varchar(30) declare @HasXIsInEffect bit2 declare @HasXOrigin bit declare @HasInheritInfo bit declare @Indexname varchar(30) declare @IncludeList nvarchar(max) declare @DebugSwitch int = 0  declare3 @ElementBufferMulti QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int SET XACT_ABORT OFF BEGIN TRY  insert into @ElementBufferMulti4 (Ident2, Bit1, Int1, Int2) select distinct x.ColumnName, x.HasXIsInEffect, x.HasXOrigin, x.HasInheritInfo from ( select tc.TableName, c.ColumnName , sign5(len(isnull(ce.UID_DialogColumn, ''))) as HasXIsInEffect , sign(len(isnull(co.UID_DialogColumn, ''))) as HasXOrigin , sign(len(isnull(ci.UID_DialogColumn6, ''))) as HasInheritInfo from QBM_VQBMRelation r join DialogColumn c with (readpast) on r.UID_ChildColumn = c.UID_DialogColumn join DialogTable tc with7 (readpast) on c.UID_DialogTable = tc.UID_DialogTable and tc.TableType in ('B', 'T')  left outer join DialogColumn co with (readpast) on co.UID_DialogTable8 = tc.UID_DialogTable and co.ColumnName = 'XOrigin' left outer join DialogColumn ce with (readpast) on tc.UID_DialogTable = ce.UID_DialogTable and ce.ColumnName9 = 'XIsInEffect' left outer join DialogColumn ci with (readpast) on tc.UID_DialogTable = ci.UID_DialogTable and ci.ColumnName = 'InheritInfo' where r.IsMNRelation10 = 1 and tc.TableName = @TableName union all select tc.TableName, c.ColumnName , 0 as HasXIsInEffect , 0 as HasXOrigin , sign(len(isnull(ci.UID_DialogColumn11, ''))) as HasInheritInfo from QBM_VQBMRelation r join DialogColumn c with (readpast) on r.UID_ChildColumn = c.UID_DialogColumn join DialogTable tc with12 (readpast) on c.UID_DialogTable = tc.UID_DialogTable and tc.TableType in ('B', 'T') left outer join DialogColumn co with (readpast) on co.UID_DialogTable13 = tc.UID_DialogTable and co.ColumnName = 'XOrigin' left outer join DialogColumn ce with (readpast) on tc.UID_DialogTable = ce.UID_DialogTable and ce.ColumnName14 = 'XIsInEffect' left outer join DialogColumn ci with (readpast) on tc.UID_DialogTable = ci.UID_DialogTable and ci.ColumnName = 'InheritInfo' where r.IsMNRelation15 = 0 and tc.TableName = @TableName  union all select tc.TableName, c.ColumnName , sign(len(isnull(ce.UID_DialogColumn, ''))) as HasXIsInEffect , sign(len16(isnull(co.UID_DialogColumn, ''))) as HasXOrigin , sign(len(isnull(ci.UID_DialogColumn, ''))) as HasInheritInfo from DialogTable tc with (readpast) join17 QBM_VQBMRelation r on r.UID_DialogTableChild = tc.UID_DialogTable and tc.TableType in ('B', 'T') join DialogColumn c with (readpast) on c.UID_DialogColumn18 = r.UID_ChildColumn left outer join DialogColumn co with (readpast) on co.UID_DialogTable = tc.UID_DialogTable and co.ColumnName = 'XOrigin' left outer19 join DialogColumn ce with (readpast) on tc.UID_DialogTable = ce.UID_DialogTable and ce.ColumnName = 'XIsInEffect' left outer join DialogColumn ci with20 (readpast) on tc.UID_DialogTable = ci.UID_DialogTable and ci.ColumnName = 'InheritInfo' where tc.IsMAllTable = 1 and tc.TableName = @TableName ) as x 21 join sys.tables ts with (nolock) on x.TableName = ts.name collate database_default and ts.type = 'U'  join sys.schemas s with (nolock) on ts.schema_id22 = s.schema_id and s.name = 'dbo' join sys.columns cs with (nolock) on ts.object_id = cs.object_id and cs.name = x.ColumnName collate database_default 23where not exists (Select top 1 1 from QBM_VHeavyLoadTables h where h.TableName = x.TableName and h.IsNoAutoIndex = 1 ) order by 1,2 select @ElementCount24 = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @columnname = bu.Ident2 , @HasXIsInEffect = bu.Bit1 , @HasXOrigin25 = bu.Int1 , @HasInheritInfo = bu.Int2 from @ElementBufferMulti bu where bu.ElementIndex = @ElementIndex select @Indexname = dbo.QBM_FGICodeName('XX', 26@TableName + @ColumnName ) if @DebugSwitch > 0 begin print @Indexname end select @IncludeList = '' if @HasXOrigin = 1 begin select @IncludeList = 'Xorigin'27 end if @HasXIsInEffect = 1 begin select @IncludeList = @IncludeList + ', XIsInEffect' end if @HasInheritInfo = 1 begin select @IncludeList = 'InheritInfo'28  end if @DebugSwitch > 0 begin print concat(@ColumnName, '(', @IncludeList, ')' ) end exec QBM_PIndexCreate @TableName, @Indexname, 0, @ColumnName, @IncludeList29 select @ElementIndex += 1 end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() 30RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH ende: return end 31