Back to OIM Explorer

dbo.QBM_PIndexCreateGenerated_XR

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.852 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_XR(2  @TableName varchar(30)3)4AS5BEGIN6  DECLARE @ColumnNameO varchar(30)7  DECLARE @ColumnNameF varchar(30)8  DECLARE @Indexname varchar(30)9  DECLARE @ColumnList nvarchar(max)10  DECLARE @DebugSwitch int = 011  DECLARE @ElementBufferMulti QBM_YCursorBuffer12  DECLARE @ElementCount int13  DECLARE @ElementIndex int14  SET XACT_ABORT OFF15  BEGIN TRY16    IF @TableName IN('QAMTrustee')17    BEGIN18      GOTO endLabel19    END20    IF NOT EXISTS(21      SELECT TOP 1 122      FROM sys.objects o23      WHERE24        name = 'QBMVNonUniqueMAllTable')25    BEGIN26      IF @TableName IN('QBMTaggedChange',27      'QBMColumnTranslation')28      BEGIN29        GOTO endLabel30      END31      IF NOT EXISTS(32        SELECT TOP 1 133        FROM DialogTable t34        WHERE35          t.TableName = @TableName AND36      LEFT(t.UID_DialogTable, 3) = 'QBM')37      BEGIN38        GOTO endLabel39      END40    END41    ELSE42    BEGIN43      IF @TableName IN(44        SELECT TableName45        FROM QBMVNonUniqueMAllTable)46      BEGIN47        GOTO endLabel48      END49    END50    INSERT INTO @ElementBufferMulti(Ident1,51    Ident2)52    SELECT53      x.ColumnObjectKey,54      x.ColumnNameFK55    FROM(56    SELECT57      tc.TableName,58      c.ColumnName AS ColumnObjectKey,59      cf.ColumnName AS ColumnNameFK60    FROM DialogTable tc61      WITH(readpast)62    JOIN DialogColumn c63      WITH(readpast)64      ON c.UID_DialogTable = tc.UID_DialogTable AND tc.TableType IN('B', 'T') AND c.SchemaDataLen = 138 AND c.ColumnName <> 'XObjectKey'65    JOIN INFORMATION_SCHEMA.columns obc66      WITH(readpast)67      ON obc.TABLE_NAME = tc.TableName AND obc.COLUMN_NAME = c.ColumnName AND obc.IS_NULLABLE = 'NO'68    JOIN DialogColumn cf69      WITH(readpast)70      ON cf.UID_DialogTable = tc.UID_DialogTable AND cf.SchemaDataLen = 38 AND cf.IsPKMember = 071    JOIN INFORMATION_SCHEMA.columns obf72      WITH(readpast)73      ON obf.TABLE_NAME = tc.TableName AND obf.COLUMN_NAME = cf.ColumnName AND obf.IS_NULLABLE = 'NO'74    WHERE75      tc.IsMAllTable = 1 AND tc.TableName = @TableName) AS x76    JOIN sys.tables ts77      WITH(nolock)78      ON x.TableName = ts.name collate database_default AND ts.type = 'U'79    JOIN sys.schemas s80      WITH(nolock)81      ON ts.schema_id = s.schema_id AND s.name = 'dbo'82    WHERE83      NOT EXISTS(84    SELECT TOP 1 185    FROM QBM_VHeavyLoadTables h86    WHERE87      h.TableName = x.TableName AND h.IsNoAutoIndex = 1)88    ORDER BY 1,89    290    SELECT @ElementCount = @@ROWCOUNT91    SELECT @ElementIndex = 192    WHILE @ElementIndex <= @ElementCount93    BEGIN94      SELECT95        TOP 1 @ColumnNameO = bu.Ident1,96        @ColumnNameF = bu.Ident297      FROM @ElementBufferMulti bu98      WHERE99        bu.ElementIndex = @ElementIndex100      SELECT101        @Indexname = dbo.QBM_FGICodeName('XR',102        CONCAT(@TableName, @ColumnNameO, @ColumnNameF))103      IF @DebugSwitch > 0104      BEGIN105        print @Indexname106      END107      SELECT108        @columnList = @ColumnNameO + ', ' + @ColumnNameF109      IF @DebugSwitch > 0110      BEGIN111        print @columnList112      END113      EXEC QBM_PIndexCreate @TableName,114        @Indexname,115      1,116        @columnList117      SELECT @ElementIndex += 1118    END119  END TRY120  BEGIN CATCH121    EXEC QBM_PSessionErrorAdd DEFAULT122    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()123    RAISERROR(@Rethrow,124    18,125    1)126      WITH NOWAIT127  END CATCH128  endLabel:129  RETURN130END

Open raw exported source

1   create   procedure QBM_PIndexCreateGenerated_XR (@TableName varchar(30) ) as begin declare @ColumnNameO varchar(30) declare @ColumnNameF varchar2(30) declare @Indexname varchar(30) declare @ColumnList nvarchar(max) declare @DebugSwitch int = 0  declare @ElementBufferMulti QBM_YCursorBuffer declare3 @ElementCount int declare @ElementIndex int SET XACT_ABORT OFF BEGIN TRY if @TableName in ('QAMTrustee') begin goto endLabel end if not exists (select4 top 1 1 from sys.objects o where name = 'QBMVNonUniqueMAllTable' ) begin if @TableName in ('QBMTaggedChange', 'QBMColumnTranslation') begin goto endLabel5 end  if not exists (select top 1 1 from DialogTable t where t.TableName = @TableName and left(t.UID_DialogTable, 3) = 'QBM' ) begin goto endLabel end 6end else begin if @TableName in (select TableName from QBMVNonUniqueMAllTable) begin goto endLabel end end   insert into @ElementBufferMulti (Ident1, Ident27) select x.ColumnObjectKey,x.ColumnNameFK from (  select tc.TableName, c.ColumnName as ColumnObjectKey, cf.ColumnName as ColumnNameFK from DialogTable 8tc with (readpast) join DialogColumn c with (readpast) on c.UID_DialogTable = tc.UID_DialogTable and tc.TableType in ('B', 'T') and c.SchemaDataLen = 1389 and c.ColumnName <> 'XObjectKey' join INFORMATION_SCHEMA.columns obc with (readpast) on obc.TABLE_NAME = tc.TableName and obc.COLUMN_NAME = c.ColumnName10 and obc.IS_NULLABLE = 'NO' join DialogColumn cf with (readpast) on cf.UID_DialogTable = tc.UID_DialogTable and cf.SchemaDataLen = 38 and cf.IsPKMember11 = 0 join INFORMATION_SCHEMA.columns obf with (readpast) on obf.TABLE_NAME = tc.TableName and obf.COLUMN_NAME = cf.ColumnName and obf.IS_NULLABLE = 'NO'12 where tc.IsMAllTable = 1 and tc.TableName = @TableName ) as x  join sys.tables ts with (nolock) on x.TableName = ts.name collate database_default and 13ts.type = 'U'  join sys.schemas s with (nolock) on ts.schema_id = s.schema_id and s.name = 'dbo' where not exists (Select top 1 1 from QBM_VHeavyLoadTables14 h where h.TableName = x.TableName and h.IsNoAutoIndex = 1 ) order by 1,2 select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex15 <= @ElementCount begin select top 1 @ColumnNameO = bu.Ident1 , @ColumnNameF = bu.Ident2 from @ElementBufferMulti bu where bu.ElementIndex = @ElementIndex16 select @Indexname = dbo.QBM_FGICodeName('XR', concat(@TableName , @ColumnNameO , @ColumnNameF)) if @DebugSwitch > 0 begin print @Indexname end select 17@columnList = @ColumnNameO + ', ' + @ColumnNameF if @DebugSwitch > 0 begin print @columnList end exec QBM_PIndexCreate @TableName, @Indexname, 1, @columnList18 select @ElementIndex += 1 end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() 19RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: return end 20