Back to OIM Explorer

dbo.QBM_PIndexCreateGenerated_XO

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.885 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_FSQIndexDef source text reference
references source dbo.QBM_PIndexCreate source text reference
references source dbo.QBM_PIndexCreateGenerated source text reference

References

Referenced By

Complete Source

1CREATE PROCEDURE QBM_PIndexCreateGenerated_XO(2  @TableNamePattern varchar(30)3)4AS5BEGIN6  DECLARE @ColumnName varchar(30)7  DECLARE @Tablename varchar(30)8  DECLARE @Indexname varchar(30)9  DECLARE @ElementBufferMulti QBM_YCursorBuffer10  DECLARE @ElementCount int11  DECLARE @ElementIndex int12  DECLARE @HasXIsInEffect BIT13  DECLARE @HasXOrigin BIT14  DECLARE @HasInheritInfo BIT15  DECLARE @XList nvarchar(max)16  DECLARE @DebugSwitch int = 017  SET XACT_ABORT OFF18  BEGIN TRY19    INSERT INTO @ElementBufferMulti(Ident2,20    Ident1,21    Bit1,22    Int1,23    Int2)24    SELECT25      c.COLUMN_NAME,26      c.TABLE_NAME,27      sign(len(isnull(ce.COLUMN_NAME, ''))) AS HasXIsInEffect,28      sign(len(isnull(co.COLUMN_NAME, ''))) AS HasXOrigin,29      sign(len(isnull(ci.COLUMN_NAME, ''))) AS HasInheritInfo30    FROM INFORMATION_SCHEMA.COLUMNS c31      WITH(readpast)32    JOIN sys.tables ts33      WITH(readpast)34      ON c.TABLE_NAME = ts.name collate database_default AND ts.type = 'U'35    JOIN sys.schemas s36      WITH(readpast)37      ON ts.schema_id = s.schema_id AND s.name = 'dbo'38    JOIN sys.columns cs39      WITH(readpast)40      ON ts.object_id = cs.object_id AND cs.name = c.COLUMN_NAME collate database_default41    LEFT42    OUTER43    JOIN INFORMATION_SCHEMA.COLUMNS co44      WITH(readpast)45      ON co.TABLE_NAME = c.TABLE_NAME AND co.Column_Name = 'XOrigin'46    LEFT47    OUTER48    JOIN INFORMATION_SCHEMA.COLUMNS ce49      WITH(readpast)50      ON c.TABLE_NAME = ce.TABLE_NAME AND ce.COLUMN_NAME = 'XIsInEffect'51    LEFT52    OUTER53    JOIN INFORMATION_SCHEMA.COLUMNS ci54      WITH(readpast)55      ON c.TABLE_NAME = ci.TABLE_NAME AND ci.COLUMN_NAME = 'InheritInfo'56    WHERE57      c.CHARACTER_MAXIMUM_LENGTH = 138 AND c.COLUMN_NAME = 'XObjectKey' AND c.TABLE_NAME LIKE @TableNamePattern AND ts.is_memory_optimized = 058    ORDER BY 159    SELECT @ElementCount = @@ROWCOUNT60    SELECT @ElementIndex = 161    WHILE @ElementIndex <= @ElementCount62    BEGIN63      SELECT64        TOP 1 @ColumnName = bu.Ident2,65        @Tablename = bu.Ident1,66        @HasXIsInEffect = bu.Bit1,67        @HasXOrigin = bu.Int1,68        @HasInheritInfo = bu.Int269      FROM @ElementBufferMulti bu70      WHERE71        bu.ElementIndex = @ElementIndex72      SELECT @XList = ''73      IF @HasXOrigin = 174      BEGIN75        SELECT @XList= 'Xorigin'76      END77      IF @HasXIsInEffect = 178      BEGIN79        SELECT80          @XList = @XList + ', XIsInEffect'81      END82      IF @HasInheritInfo = 183      BEGIN84        SELECT @XList = 'InheritInfo'85      END86      SELECT87        @Indexname = dbo.QBM_FGICodeName('XO',88        @TableName + @ColumnName)89      IF @DebugSwitch > 090      BEGIN91        print @indexname92        SELECT93          dbo.QBM_FSQIndexDef(@Tablename,94          '%') print CONCAT(@ColumnName,95          '(',96          @XList,97          ')')98      END99      EXEC QBM_PIndexCreate @TableName,100        @indexname,101      1,102        @columnname,103        @XList104      SELECT @ElementIndex += 1105    END106  END TRY107  BEGIN CATCH108    DECLARE @ErrorMessage nvarchar(4000)109    DECLARE @ErrorSeverity int110    DECLARE @ErrorState int111    SELECT @ErrorSeverity = ERROR_SEVERITY()112    IF @ErrorSeverity > 18113    BEGIN114      SELECT @ErrorSeverity = 18115    END116    SELECT @ErrorState = 1117    SELECT118      @ErrorMessage = isnull(ERROR_MESSAGE(),119      '<no message>')120    RAISERROR(@ErrorMessage,121    @ErrorSeverity,122    @ErrorState)123      WITH NOWAIT124  END CATCH125  ende:126  RETURN127END

Open raw exported source

1    create   procedure QBM_PIndexCreateGenerated_XO (@TableNamePattern varchar(30) ) as begin declare @ColumnName varchar(30) declare @Tablename2 varchar(30) declare @Indexname varchar(30) declare @ElementBufferMulti QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @HasXIsInEffect3 bit declare @HasXOrigin bit declare @HasInheritInfo bit declare @XList nvarchar(max) declare @DebugSwitch int = 0 SET XACT_ABORT OFF BEGIN TRY  insert4 into @ElementBufferMulti (Ident2, Ident1, Bit1, Int1, Int2) select c.COLUMN_NAME, c.TABLE_NAME , sign(len(isnull(ce.COLUMN_NAME, ''))) as HasXIsInEffect5 , sign(len(isnull(co.COLUMN_NAME, ''))) as HasXOrigin , sign(len(isnull(ci.COLUMN_NAME, ''))) as HasInheritInfo from INFORMATION_SCHEMA.COLUMNS c with6 (readpast)   join sys.tables ts with (readpast) on c.TABLE_NAME = ts.name collate database_default and ts.type = 'U'  join sys.schemas s with (readpast7) on ts.schema_id = s.schema_id and s.name = 'dbo' join sys.columns cs with (readpast) on ts.object_id = cs.object_id and cs.name = c.COLUMN_NAME collate8 database_default left outer join INFORMATION_SCHEMA.COLUMNS co with (readpast) on co.TABLE_NAME = c.TABLE_NAME and co.Column_Name = 'XOrigin' left outer9 join INFORMATION_SCHEMA.COLUMNS ce with (readpast) on c.TABLE_NAME = ce.TABLE_NAME and ce.COLUMN_NAME = 'XIsInEffect' left outer join INFORMATION_SCHEMA.COLUMNS10 ci with (readpast) on c.TABLE_NAME = ci.TABLE_NAME and ci.COLUMN_NAME = 'InheritInfo' where c.CHARACTER_MAXIMUM_LENGTH = 138 and c.COLUMN_NAME = 'XObjectKey'11 and c.TABLE_NAME like @TableNamePattern  and ts.is_memory_optimized = 0 order by 1 select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex12 <= @ElementCount begin select top 1 @ColumnName = bu.Ident2 , @Tablename = bu.Ident1 , @HasXIsInEffect = bu.Bit1 , @HasXOrigin = bu.Int1 , @HasInheritInfo13 = bu.Int2 from @ElementBufferMulti bu where bu.ElementIndex = @ElementIndex select @XList = '' if @HasXOrigin = 1 begin select @XList= 'Xorigin' end if14 @HasXIsInEffect = 1 begin select @XList = @XList + ', XIsInEffect' end if @HasInheritInfo = 1 begin select @XList = 'InheritInfo'  end select @Indexname15 = dbo.QBM_FGICodeName('XO', @TableName + @ColumnName ) if @DebugSwitch > 0 begin print @indexname select dbo.QBM_FSQIndexDef(@Tablename, '%') print concat16(@ColumnName, '(', @XList, ')' ) end exec QBM_PIndexCreate @TableName, @indexname, 1, @columnname , @XList select @ElementIndex += 1 end  END TRY BEGIN17 CATCH declare @ErrorMessage nvarchar(4000) declare @ErrorSeverity int declare @ErrorState int  select @ErrorSeverity = ERROR_SEVERITY() if @ErrorSeverity18 > 18 begin select @ErrorSeverity = 18 end select @ErrorState = 1 select @ErrorMessage = isnull(ERROR_MESSAGE() , '<no message>') RAISERROR (@ErrorMessage19, @ErrorSeverity, @ErrorState) WITH NOWAIT END CATCH ende: return end 20