Back to OIM Explorer

dbo.QBM_PTableReload

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Single DBQueue insert -> QBM-K-CommonReCalculate / QBM_ZRecalculate at line 40; Single DBQueue insert -> QBM-K-CommonReCalculate / QBM_ZRecalculate at line 40; Single DBQueue insert -> QBM-K-CommonMakeWatchtrigger / QBM_ZTriggerWatchCreate at line 40; Single DBQueue insert -> QBM-K-SetRowLockOnly / QBM_ZSetRowLockOnly at line 40

Source: sandbox-db sys.sql_modules

Source size: 5.903 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.
  • DBQueue relation detected. Follow the task procedure and referenced-by list for async processing.

Relations

  • Single DBQueue insert -> QBM-K-CommonReCalculate / QBM_ZRecalculate at line 40
  • Single DBQueue insert -> QBM-K-CommonReCalculate / QBM_ZRecalculate at line 40
  • Single DBQueue insert -> QBM-K-CommonMakeWatchtrigger / QBM_ZTriggerWatchCreate at line 40
  • Single DBQueue insert -> QBM-K-SetRowLockOnly / QBM_ZSetRowLockOnly at line 40
  • Single DBQueue insert -> QBM-K-CommonReCalculate / QBM_ZRecalculate at line 40
  • Single DBQueue insert -> QBM-K-CommonReCalculate / QBM_ZRecalculate at line 41
  • Single DBQueue insert -> QBM-K-CommonMakeWatchtrigger / QBM_ZTriggerWatchCreate at line 41
  • Single DBQueue insert -> QBM-K-SetRowLockOnly / QBM_ZSetRowLockOnly at line 41
  • Single DBQueue insert -> QBM-K-CommonReCalculate / QBM_ZRecalculate at line 41
  • Single DBQueue insert -> QBM-K-SetRowLockOnly / QBM_ZSetRowLockOnly at line 42
  • Single DBQueue insert -> QBM-K-CommonReCalculate / QBM_ZRecalculate at line 42
  • References QBM_PDBQueueInsert_Single

Typed Edges

  • queues DBQueue task QBM_ZRecalculate at line 40 Single DBQueue insert -> QBM-K-CommonReCalculate / QBM_ZRecalculate at line 40
  • queues DBQueue task QBM_ZTriggerWatchCreate at line 40 Single DBQueue insert -> QBM-K-CommonMakeWatchtrigger / QBM_ZTriggerWatchCreate at line 40
  • queues DBQueue task QBM_ZSetRowLockOnly at line 40 Single DBQueue insert -> QBM-K-SetRowLockOnly / QBM_ZSetRowLockOnly at line 40
  • queues DBQueue task QBM_ZRecalculate at line 41 Single DBQueue insert -> QBM-K-CommonReCalculate / QBM_ZRecalculate at line 41
  • queues DBQueue task QBM_ZTriggerWatchCreate at line 41 Single DBQueue insert -> QBM-K-CommonMakeWatchtrigger / QBM_ZTriggerWatchCreate at line 41
  • queues DBQueue task QBM_ZSetRowLockOnly at line 41 Single DBQueue insert -> QBM-K-SetRowLockOnly / QBM_ZSetRowLockOnly at line 41
  • queues DBQueue task QBM_ZSetRowLockOnly at line 42 Single DBQueue insert -> QBM-K-SetRowLockOnly / QBM_ZSetRowLockOnly at line 42
  • queues DBQueue task QBM_ZRecalculate at line 42 Single DBQueue insert -> QBM-K-CommonReCalculate / QBM_ZRecalculate at line 42
  • references source dbo.QBM_FTSchemaDependencies source text reference
  • references source dbo.QBM_FCVStringToIndent source text reference
  • references source dbo.QBM_FGIDiskStoreExisting source text reference
  • references source dbo.QBM_FGIDiskStoreTarget source text reference
  • references source dbo.QBM_FSQColumnList source text reference
  • references source dbo.QBM_FSQIndexDef source text reference
  • references source dbo.QBM_FSQIndexDef_i source text reference
  • references source dbo.QBM_FSQTableDef source text reference
  • references source dbo.QBM_PConstraintFKDrop source text reference
  • references source dbo.QBM_PDBQueueInsert_Single source text reference
  • references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PFunctionDrop source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PTriggerDrop source text reference
  • references source dbo.QBM_PViewDrop source text reference
  • references source dbo.QBM_TUIndicator474488 source text reference
  • queues DBQueue task QBM-K-CommonReCalculate -> QBM_ZRecalculate QBM_PDBQueueInsert_Single 'QBM-K-CommonReCalculate' , 'QBM-K-MakeConstraint' , '', @Genprocid exec QBM_PDBQueueInsert_Single 'QBM-K-CommonReCalculate' , 'QBM-K-CommonMakeRITrigger', '', @Genprocid exec QBM_PDBQueueInsert_Single 'QBM-K-Comm…
  • queues DBQueue task QBM-K-CommonMakeWatchtrigger -> QBM_ZTriggerWatchCreate QBM_PDBQueueInsert_Single 'QBM-K-CommonMakeWatchtrigger', @TableName , '', @Genprocid exec QBM_PDBQueueInsert_Single 'QBM-K-SetRowLockOnly', '', '', @Genprocid exec QBM_PDBQueueInsert_Single 'QBM-K-CommonReCalculate' , 'QBM-K-CommonRebuild…
  • queues DBQueue task QBM-K-SetRowLockOnly -> QBM_ZSetRowLockOnly QBM_PDBQueueInsert_Single 'QBM-K-SetRowLockOnly', '', '', @Genprocid exec QBM_PDBQueueInsert_Single 'QBM-K-CommonReCalculate' , 'QBM-K-CommonRebuildView', '', @Genprocid END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default rollback transa…

References

Referenced By

Complete Source

SQL293 lines
1CREATE PROCEDURE QBM_PTableReload(2  @TableName varchar(38),3  @WithReplace BIT,4  @Genprocid varchar(38) = NULL,5  @DiskStoreMove BIT = 06)7AS8BEGIN9  DECLARE @TableName_old varchar(30) =10  LEFT('T' + replace(newid(), '-', ''),11  30)12  DECLARE @SQLCmd nvarchar(max)13  DECLARE @CreateCmd nvarchar(max)14  DECLARE @InsertCmd nvarchar(max)15  DECLARE @IndexesAndTriggers QBM_YCursorBuffer16  DECLARE @CodeType char(1)17  DECLARE @CodeName varchar(30)18  DECLARE @i int19  DECLARE @CountIndexesAndTriggers int20  DECLARE @DebugSwitch int = 021  DECLARE @DebugLevel char(1) = 'W'22  DECLARE @Debugmessage nvarchar(1000)23  DECLARE @Productionlevel int24  SET XACT_ABORT OFF25  BEGIN TRY26    IF @Genprocid IS NULL27    BEGIN28      SELECT @Genprocid = NEWID()29    END30    IF EXISTS(31      SELECT TOP 1 132      FROM sys.views v33      WHERE34        v.name = 'QBM_VTablesReloadAble')35    BEGIN36      IF NOT EXISTS(37        SELECT TOP 1 138        FROM QBM_VTablesReloadAble v39        WHERE40          v.TableName = @TableName)41      BEGIN42        GOTO endLabel43      END44    END45    IF @DiskStoreMove = 046    BEGIN47      IF @TableName IN(48        SELECT v.TableName49        FROM QBM_VHeavyLoadTables v50        WHERE51          v.IsNoReload = 1)52      BEGIN53        GOTO endLabel54      END55    END56    SELECT TOP 1 @Productionlevel = db.ProductionLevel57    FROM DialogDatabase db58      WITH(readpast)59    WHERE60      db.IsMainDatabase = 161    IF @Productionlevel > 1 AND @WithReplace = 162    BEGIN63      RAISERROR('#LDS#Replacement is not permitted in production environment|',64      18,65      1)66        WITH nowait67      GOTO EndLabel68    END69    IF @DiskStoreMove = 0 AND dbo.QBM_FGIDiskStoreExisting(@TableName) <> dbo.QBM_FGIDiskStoreTarget(@TableName)70    BEGIN71      GOTO endlabel72    END73    IF @DiskStoreMove = 1 AND @TableName IN(74      SELECT TableName75      FROM QBM_VStartupTables76      UNION77      SELECT 'QBMDiskStoreLogical'78      UNION79      SELECT 'QBMDiskStorePhysical')80    BEGIN81      GOTO endLabel82    END83    BEGIN84      TRANSACTION85      INSERT INTO @IndexesAndTriggers(ContentFull,86      Ident1,87      Ident2)88      SELECT89        dbo.QBM_FSQIndexDef_i(t.name,90        i.name,91        1),92        'I',93        LEFT(i.name,94        30)95      FROM sys.tables t96      JOIN sys.indexes i97        ON t.object_id = i.object_id98      WHERE99        t.name = @TableName AND i.is_primary_key = 0 AND i.is_hypothetical = 0 AND i.type NOT IN(3,100      4,101      5,102      6)103      SELECT @CountIndexesAndTriggers = @@ROWCOUNT104      INSERT INTO @IndexesAndTriggers(ContentFull,105      Ident1,106      Ident2)107      SELECT108        m.definition,109        'T',110        LEFT(tr.name,111        30)112      FROM sys.tables t113      JOIN sys.triggers tr114        ON t.object_id = tr.parent_id115      JOIN sys.sql_modules m116        ON m.object_id = tr.object_id117      WHERE118        t.name = @TableName AND tr.name NOT LIKE 'GEN[_]%' AND tr.name != 'QBM_TUIndicator474488'119      SELECT @CountIndexesAndTriggers += @@ROWCOUNT120      INSERT INTO @IndexesAndTriggers(ContentFull,121      Ident1,122      Ident2)123      SELECT124        f.SourceCode,125        f.ObjectType,126        f.ObjectName127      FROM dbo.QBM_FTSchemaDependencies(@TableName,128      1) f129      WHERE130        f.ObjectType IN('F',131      'V')132      SELECT @CountIndexesAndTriggers += @@ROWCOUNT133      SELECT @i = 1134      WHILE @i <= @CountIndexesAndTriggers135      BEGIN136        SELECT137          TOP 1 @CodeType = i.Ident1,138          @CodeName = i.Ident2139        FROM @IndexesAndTriggers i140        WHERE141          i.ElementIndex = @i142        IF @CodeType = 'F'143        BEGIN144          IF @DebugSwitch > 0145          BEGIN146            print 'dropping funct ' + @CodeName147          END148          EXEC QBM_PFunctionDrop @CodeName149        END150        IF @CodeType = 'V'151        BEGIN152          IF @DebugSwitch > 0153          BEGIN154            print 'dropping view  ' + @CodeName155          END156          EXEC QBM_PViewDrop @CodeName157        END158        SELECT @i += 1159      END160      EXEC QBM_PTriggerDrop '%',161        @TableName,162        @CustomTriggerAlso = 1163      EXEC QBM_PConstraintFKDrop @TableName,164      '%',165      '%'166      EXEC QBM_PConstraintFKDrop '%',167      '%',168        @TableName169      SELECT @CreateCmd = dbo.QBM_FSQTableDef(@TableName)170      SELECT171        @InsertCmd = 'Insert into ' + @TableName + '172				(173		' + dbo.QBM_FCVStringToIndent(dbo.QBM_FSQColumnList(@TableName,174        0, 1),175        4) + '176				) 177		select 178		' + dbo.QBM_FCVStringToIndent(dbo.QBM_FSQColumnList(@TableName, @WithReplace,179        1),180        4) + '181		from ' + @TableName_old182      IF @DebugSwitch > 0183      BEGIN184        print 'TableName ' + isnull(@TableName,185        '#') print '@TableName_old ' + isnull(@TableName_old,186        '#')187      END188      EXEC sp_rename @TableName,189        @TableName_old,190      'object'191      IF @DebugSwitch > 0192      BEGIN193        print @CreateCmd194      END195      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @CreateCmd,196        @LockTimeout_ms = DEFAULT,197        @MaxWaitTimeForLock_s = DEFAULT,198        @ProcIDForJournal = @@PROCID,199        @HandleErrorSilent = 0200      IF @DebugSwitch > 0201      BEGIN202        print @Insertcmd203      END204      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @insertcmd,205        @LockTimeout_ms = DEFAULT,206        @MaxWaitTimeForLock_s = DEFAULT,207        @ProcIDForJournal = @@PROCID,208        @HandleErrorSilent = 0209      IF @DebugSwitch > 0210      BEGIN211        SELECT212          i.ElementIndex,213          i.Ident2214        FROM @IndexesAndTriggers i215      END216      SELECT @i = 1217      WHILE @i <= @CountIndexesAndTriggers218      BEGIN219        SELECT220          TOP 1 @SQLCmd = i.ContentFull,221          @CodeType = i.Ident1,222          @CodeName = i.Ident2223        FROM @IndexesAndTriggers i224        WHERE225          i.ElementIndex = @i226        IF @DebugSwitch > 0227        BEGIN228          print @CodeType + ' ' + @Codename229        END230        IF @CodeType = 'T'231        BEGIN232          EXEC sp_executesql @sqlcmd233        END234        IF @CodeType = 'I'235        BEGIN236          EXEC sp_executesql @sqlcmd237        END238        IF @CodeType = 'F'239        BEGIN240          EXEC sp_executesql @sqlcmd241        END242        IF @CodeType = 'V'243        BEGIN244          EXEC sp_executesql @sqlcmd245        END246        SELECT @i += 1247      END248      SELECT @SQLCmd = 'drop table ' + @TableName_old249      IF @DebugSwitch > 0250      BEGIN251        print @SQLCmd252      END253      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @sqlcmd,254        @LockTimeout_ms = DEFAULT,255        @MaxWaitTimeForLock_s = DEFAULT,256        @ProcIDForJournal = @@PROCID,257        @HandleErrorSilent = 0258      SELECT @Debugmessage = 'reload table ' + @TableName259      EXEC QBM_PJournal @debugmessage,260        @@procid,261      'D',262        @DebugLevel COMMIT TRANSACTION263      EXEC QBM_PDBQueueInsert_Single 'QBM-K-CommonReCalculate',264      'QBM-K-MakeConstraint',265      '',266        @Genprocid267      EXEC QBM_PDBQueueInsert_Single 'QBM-K-CommonReCalculate',268      'QBM-K-CommonMakeRITrigger',269      '',270        @Genprocid271      EXEC QBM_PDBQueueInsert_Single 'QBM-K-CommonMakeWatchtrigger',272        @TableName,273      '',274        @Genprocid275      EXEC QBM_PDBQueueInsert_Single 'QBM-K-SetRowLockOnly',276      '',277      '',278        @Genprocid279      EXEC QBM_PDBQueueInsert_Single 'QBM-K-CommonReCalculate',280      'QBM-K-CommonRebuildView',281      '',282        @Genprocid283    END TRY284    BEGIN CATCH285      EXEC QBM_PSessionErrorAdd DEFAULT ROLLBACK TRANSACTION286      RAISERROR('',287      18,288      1)289        WITH NOWAIT290    END CATCH291    endLabel:292    RETURN293  END
Open raw exported source
SQL · Raw45 lines
1       create   procedure QBM_PTableReload (@TableName varchar(38) , @WithReplace bit  , @Genprocid varchar(38) = null , @DiskStoreMove bit = 0 2 ) as begin declare @TableName_old varchar(30) = left('T'+ replace(newid(), '-', ''), 30) declare @SQLCmd nvarchar(max) declare @CreateCmd nvarchar(max3) declare @InsertCmd nvarchar(max) declare @IndexesAndTriggers QBM_YCursorBuffer     declare @CodeType char(1) declare @CodeName varchar(30) declare @i4 int declare @CountIndexesAndTriggers int declare @DebugSwitch int = 0 declare @DebugLevel char(1) = 'W' declare @Debugmessage nvarchar(1000) declare @Productionlevel5 int SET XACT_ABORT OFF BEGIN TRY if @Genprocid is null begin select @Genprocid = NEWID() end if exists (select top 1 1 from sys.views v where v.name =6 'QBM_VTablesReloadAble' ) begin if not exists (select top 1 1 from QBM_VTablesReloadAble v where v.TableName = @TableName ) begin goto endLabel end end7 if  @DiskStoreMove = 0 begin if @TableName in ( select v.TableName from QBM_VHeavyLoadTables v where v.IsNoReload = 1 ) begin goto endLabel end end  select8 top 1 @Productionlevel = db.ProductionLevel from DialogDatabase db with (readpast) where db.IsMainDatabase = 1 if @Productionlevel > 1 and @WithReplace9 = 1  begin raiserror('#LDS#Replacement is not permitted in production environment|', 18, 1) with nowait goto EndLabel end if @DiskStoreMove = 0 and dbo.QBM_FGIDiskStoreExisting10(@TableName) <> dbo.QBM_FGIDiskStoreTarget(@TableName) begin goto endlabel end if @DiskStoreMove = 1 and @TableName in (select TableName from QBM_VStartupTables11 union select 'QBMDiskStoreLogical' union select 'QBMDiskStorePhysical' ) begin  goto endLabel end begin transaction  insert into @IndexesAndTriggers(ContentFull12,Ident1 , Ident2 ) select dbo.QBM_FSQIndexDef_i (t.name, i.name, 1), 'I', left(i.name, 30) from sys.tables t join sys.indexes i on t.object_id = i.object_id13 where t.name = @TableName and i.is_primary_key = 0 and i.is_hypothetical = 0  and i.type not in (3,4,5,6) select @CountIndexesAndTriggers = @@ROWCOUNT14    insert into @IndexesAndTriggers(ContentFull, Ident1 , Ident2 ) select m.definition, 'T', left(tr.name, 30) from sys.tables t join sys.triggers tr on15 t.object_id = tr.parent_id join sys.sql_modules m on m.object_id = tr.object_id where t.name = @TableName  and tr.name not like 'GEN[_]%'  and tr.name16 != 'QBM_TUIndicator474488' select @CountIndexesAndTriggers += @@ROWCOUNT  insert into @IndexesAndTriggers(ContentFull, Ident1 , Ident2 ) select f.SourceCode17, f.ObjectType, f.ObjectName from dbo.QBM_FTSchemaDependencies(@TableName, 1) f where f.ObjectType in ('F', 'V') select @CountIndexesAndTriggers += @@ROWCOUNT18 select @i = 1 while @i <= @CountIndexesAndTriggers begin select top 1 @CodeType = i.Ident1  , @CodeName = i.Ident2  from @IndexesAndTriggers i where i.ElementIndex19 = @i if @CodeType = 'F' begin if @DebugSwitch > 0 begin print 'dropping funct ' + @CodeName end  exec QBM_PFunctionDrop @CodeName end if @CodeType = 'V'20 begin if @DebugSwitch > 0 begin print 'dropping view  ' + @CodeName end exec QBM_PViewDrop @CodeName end select @i += 1 end    exec QBM_PTriggerDrop '%'21, @TableName , @CustomTriggerAlso = 1 exec QBM_PConstraintFKDrop @TableName , '%', '%' exec QBM_PConstraintFKDrop '%', '%', @TableName  select @CreateCmd22 = dbo.QBM_FSQTableDef(@TableName ) select @InsertCmd = 'Insert into ' + @TableName + '23				(24		' + dbo.QBM_FCVStringToIndent( dbo.QBM_FSQColumnList(@TableName25 , 0, 1), 4) + '26				) 27		select 28		' + dbo.QBM_FCVStringToIndent( dbo.QBM_FSQColumnList(@TableName , @WithReplace, 1), 4) + '29		from ' + @TableName_old30 if @DebugSwitch > 0 begin print 'TableName ' + isnull(@TableName, '#') print '@TableName_old ' + isnull(@TableName_old, '#') end  exec sp_rename @TableName31 , @TableName_old, 'object' if @DebugSwitch > 0 begin print @CreateCmd end exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @CreateCmd , @LockTimeout_ms32 = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@PROCID , @HandleErrorSilent = 0  if @DebugSwitch > 0 begin print @Insertcmd end exec33 QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @insertcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@PROCID ,34 @HandleErrorSilent = 0 if @DebugSwitch > 0 begin select i.ElementIndex, i.Ident2  from @IndexesAndTriggers i end  select @i = 1 while @i <= @CountIndexesAndTriggers35 begin select top 1 @SQLCmd = i.ContentFull , @CodeType = i.Ident1  , @CodeName = i.Ident2  from @IndexesAndTriggers i where i.ElementIndex = @i if @DebugSwitch36 > 0 begin print @CodeType + ' ' + @Codename end if @CodeType = 'T' begin   exec sp_executesql @sqlcmd end if @CodeType = 'I' begin exec sp_executesql 37@sqlcmd end if @CodeType = 'F' begin exec sp_executesql @sqlcmd end if @CodeType = 'V' begin exec sp_executesql @sqlcmd end select @i += 1 end   select38 @SQLCmd = 'drop table ' + @TableName_old if @DebugSwitch > 0 begin print @SQLCmd end exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @sqlcmd , @LockTimeout_ms39 = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@PROCID , @HandleErrorSilent = 0 select @Debugmessage = 'reload table ' + @TableName40 exec QBM_PJournal @debugmessage, @@procid, 'D', @DebugLevel commit transaction  exec QBM_PDBQueueInsert_Single 'QBM-K-CommonReCalculate' , 'QBM-K-MakeConstraint'41, '', @Genprocid exec QBM_PDBQueueInsert_Single 'QBM-K-CommonReCalculate' , 'QBM-K-CommonMakeRITrigger', '', @Genprocid exec QBM_PDBQueueInsert_Single 42'QBM-K-CommonMakeWatchtrigger', @TableName , '', @Genprocid exec QBM_PDBQueueInsert_Single 'QBM-K-SetRowLockOnly', '', '', @Genprocid exec QBM_PDBQueueInsert_Single43 'QBM-K-CommonReCalculate' , 'QBM-K-CommonRebuildView', '', @Genprocid END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default rollback transaction RAISERROR44 ('', 18, 1) WITH NOWAIT END CATCH endLabel: return end 45